Using a macro to loop through files and process data and output into a destination file

[rogerjdeangelis]

The advantage of dosubl is two way communication with a child process.


%symdel err obs_address ;
data _null_;
  retain obs_address 0;
  set sashelp.vtable (where=(libname="SASHELP" and memname=:"MAC"));
  call symputx('memname',memname);
  call symputx("obs_address",put(addrlong(obs_address),hex16.));
  rc=dosubl('
    proc sql;
      create table WANT_&memname as
      select *
      from   sashelp.&memname
    ;quit;
     data _null_;
        call pokelong(&sqlobs,"&obs_address"x,8);
     run;quit;
    ');
    put memname  obs_address=;
    if obs_address = 0 then stop;
run;quit;


NOTE: Table WORK.WANT_MACRS7 created, with 8 rows and 2 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:41 AM
      Step Count                        430  Switch Count  0


SYMBOLGEN:  Macro variable SQLOBS resolves to 8
SYMBOLGEN:  Macro variable OBS_ADDRESS resolves to D8E18A0A00000000
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:42 AM
      Step Count                        430  Switch Count  0


MACRS7 OBS_ADDRESS=8
NOTE: There were 6 observations read from the data set SASHELP.VTABLE.
      WHERE (libname='SASHELP') and (memname=:'MAC');
NOTE: DATA statement used (Total process time):
      real time           1.64 seconds
      user cpu time       0.43 seconds
      system cpu time     0.90 seconds
      memory              7453.53k
      OS Memory           27300.00k
      Timestamp           12/14/2016 08:52:42 AM
      Step Count                        430  Switch Count  12

2905!     quit;

[ken2 · Accepted solution]

Thanks for your suggestion. I moved the target table into the macro! And rewrote the loop code, now it's totally solved!

%macro Summary(Unit=, Year=);

/* %do Year=2014 %to 2015;*/

proc sql;

create table &Unit._&Year._Summary as

select Unit,

Year,

count(Client) as Total_Client,

sum(Sales) as Total_Sales,

sum(Sales)/count(Client) as Average_Sales

from &Unit._&Year._Itemized

group by Unit, Year;

quit;

proc append data = &Unit._&Year._Summary

base = Total_Summary /* This is the real target result table, created and appended on the run */

force;

run;

proc datasets lib = WORK memtype = data nolist;

delete &Unit._&Year._Summary;

run;

/* %end; */

%mend Summary;

data STEERING_TABLE; /* This table is only the result of the running, can be deleted after run */

length UNIT $12 YEAR 8;

do Unit='IT', 'Support';

do Year=2014 to 2015;

rc=dosubl('%Summary(Unit=' || UNIT || ', Year=' || YEAR || ');');

output;

end;

end;

run;