Wednesday, July 20, 2016

SAS: Data Step Merge example



Source:


* Merge example ;

data a;
input key1 key2 aonly both;
datalines;
1 1 1 1
1 2 2 2
3 1 3 3
3 2 6 6
5 1 5 5
;
run;


data b;
input key1 key2 bonly both;
datalines;
1 2 8 22  expect change var both from 2 to 22
3 3 8 3   expect insert
5 1 8 55  expect change var both from 5 to 55
;
run;


data merged;
merge a b;
by key1 key2;
run;


proc print data=merged;
run;



Results:


Obs
key1
key2
aonly
both
bonly
1
1
1
1
1
.
2
1
2
2
22
8
3
3
1
3
3
.
4
3
2
6
6
.
5
3
3
.
3
8
6
5
1
5
55
8

Monday, July 18, 2016

SAS: PROC SQL, Delete from one table based on contents of another table

Source:



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