Hi All,
Looking for some assistance in rewriting this code to use a macro and/or do loops as the process is too long & cumbersome.
I'm using SAS EG7
proc sql;
create table test_201801 as
select f1,f2,f3,'201801' as YM
from mylib.detail a
left join dim.hl_20180131 b
on a.f1=b.f1
where f2=201801;
quit;
proc sql;
create table test_201802 as
select f1,f2,f3,'201802' as YM
from mylib.detail a
left join dim.hl_20180228 b
on a.f1=b.f1
where f2=201802;
quit;
proc sql;
create table test_201803 as
select f1,f2,f3,'201803' as YM
from mylib.detail a
left join dim.hl_20180331 b
on a.f1=b.f1
where f2=201803;
quit;
proc sql;
create table final as
select * from test_201801
union
select * from test_201802
union
select * from test_201803;
quit;
Thank you for your assistance.
Sorry, your problem here is data modelling and handling. Using macro may work around the solution, but it will be far from ideal. For instance why do you need three sql steps (or in fact how ever many you need).
/* set all like data together */ data hl; set dim.hl: indsname=tmp; fd=substr(indsname,4,6); /* gets the 201802 part */ run; proc sql; create table want as select a.*, b.fd as ym from mylib.detail a left join hl b on a.f1=b.f1 and strip(put(a.f2,best.))=b.fd; quit;
Do you see what I have done here, rather than continuously looping over various datasets, I have put them together, and with a simple bit of logic provided the means to join both datasets together in one step = far less resource intensive, simpler coding, less disk space. If you find your having to write macro, or do lots of the same datasteps, then you can immediately tell that your data modelling is incorrect.
check something like this:
%macro Age_dist(Age_all);
%do i=1 %to %SYSFUNC(countw("&age_all"));
%let Age_=%scan("&age_all",&i,"|");
proc sql;
create table test_&Age_ as
select *
from sashelp.class
where age=&Age_;
quit;
%end;
data final;
set test_:;
run;
%mend Age_dist;
%Age_dist(12|13|14);
Thank you, that will work for me!
If you don't need those datasets you can try in single process in order creating multiple datasets.
%let Age_All=12,13,14;
PROC SQL;
create table final as
select *,put(age,3.) format=$3. as Age_Char
from sashelp.class
where age in (&Age_All);
quit;
This is similar what the other process is doing, if your target is to create only final table and don't need the rest of the tables.
Sorry, your problem here is data modelling and handling. Using macro may work around the solution, but it will be far from ideal. For instance why do you need three sql steps (or in fact how ever many you need).
/* set all like data together */ data hl; set dim.hl: indsname=tmp; fd=substr(indsname,4,6); /* gets the 201802 part */ run; proc sql; create table want as select a.*, b.fd as ym from mylib.detail a left join hl b on a.f1=b.f1 and strip(put(a.f2,best.))=b.fd; quit;
Do you see what I have done here, rather than continuously looping over various datasets, I have put them together, and with a simple bit of logic provided the means to join both datasets together in one step = far less resource intensive, simpler coding, less disk space. If you find your having to write macro, or do lots of the same datasteps, then you can immediately tell that your data modelling is incorrect.
Thanks RW9, I see your point here, macro not really required...
I have implemented your idea into my code, and it does work.
Thanks everyone for the help.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.