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

SAS: PROC SQL, FEEDBACK option

PROC SQL will show details about output files in the SAS log, but unfortunately does not (currently) list input files. Rather than attempt to parse the input code, you can probably get what you want my adding the FEEDBACK option to the PROC SQL statement.

SOURCE

data emp;

input emp $ dep $;

datalines;

Bill A

CQ A

Cora B

Peter B

Emma C

Nathan C

;

run;

%let DEP = work.dep;

data &DEP;

input dep $ dep_desc $;

datalines;

A Wasco

B Midland

C Lansing

;

quit;

run;

data hrs;

input emp $ hrs ;

datalines;

Bill 40

CQ 20

Cora 20

Peter 50

;

run;

proc sql noprint feedback;

select dep.dep, dep.dep_desc, sum(hrs.hrs) as hours

from dep, emp, hrs

where dep.dep = emp.dep

and emp.emp = hrs.emp

group by 1,2

;

quit;

run;

LOG

34   proc sql noprint feedback;

35   select dep.dep, dep.dep_desc, sum(hrs.hrs) as hours

36   from dep, emp, hrs

37   where dep.dep = emp.dep

38   and emp.emp = hrs.emp

39   group by 1,2

40   ;

NOTE: Statement transforms to:

        select DEP.dep, DEP.dep_desc, SUM(HRS.hrs) as hours

          from WORK.DEP, WORK.EMP, WORK.HRS

         where (DEP.dep = EMP.dep) and (EMP.emp = HRS.emp)

      group by 1, 2;

41   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.10 seconds

      cpu time            0.12 seconds

42   run;