data work.master;
input part warehouse qty;
datalines;
111 1 11
111 2 12
222 1 21
222 2 22
333 1 31
333 2 32
444 1 41
444 2 42
555 1 51
555 2 52
;
quit;
run;
data work.trans;
input part warehouse;
datalines;
222 1
333 2
444 1
444 3 does not exist
;
quit;
run;
title "Before
delete";
proc print
data=master;
run;
title;
title "To
be deleted";
proc print
data=trans;
run;
title;
proc sql
noprint;
delete from master
where exists (
select 1
from trans
where master.part = trans.part
and
master.warehouse = trans.warehouse
)
;
quit;
run;
title "After
delete, before rewrite";
proc print
data=master;
run;
title;
* PROC SQL delete is a logical delete only. ;
* Copy of data set will cause physical delete. ;
data master;
set master;
run;
title "After
delete, after rewrite";
proc print
data=master;
run;
title;
Results:
Before delete
Obs part warehouse qty
1 111 1 11
2 111 2 12
3 222 1 21
4 222 2 22
5 333 1 31
6 333 2 32
7 444 1 41
8 444 2 42
9 555 1 51
10 555 2 52
To be deleted
Obs part warehouse
1 222 1
2 333 2
3 444 1
4 444 3
After delete, before rewrite
Obs part warehouse qty
1 111 1 11
2 111 2 12
4 222 2 22
5 333 1 31
8 444 2 42
9 555 1 51
10 555 2 52
After delete, after rewrite
Obs part warehouse qty
1 111 1 11
2 111 2 12
3 222 2 22
4 333 1 31
5 444 2 42
6 555 1 51
7 555 2 52
No comments:
Post a Comment