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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.