topic Re: How can I create a macro/ loop to complete this task? in SAS Programming

How can I create a macro/ loop to complete this task?

DeepaG — Wed, 25 Apr 2018 12:05:50 GMT

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.

Re: How can I create a macro/ loop to complete this task?

SuryaKiran — Wed, 25 Apr 2018 12:46:14 GMT

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);

Re: How can I create a macro/ loop to complete this task?

DeepaG — Wed, 25 Apr 2018 13:03:14 GMT

Thank you, that will work for me!

Re: How can I create a macro/ loop to complete this task?

RW9 — Wed, 25 Apr 2018 13:24:56 GMT

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.

Re: How can I create a macro/ loop to complete this task?

SuryaKiran — Wed, 25 Apr 2018 15:44:50 GMT

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.

Re: How can I create a macro/ loop to complete this task?

DeepaG — Thu, 26 Apr 2018 09:19:51 GMT

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.