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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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.


Accepted Solutions
Solution
‎04-26-2018 05:20 AM
Super User
Super User
Posts: 9,599

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

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.

View solution in original post


All Replies
Valued Guide
Posts: 597

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

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);
Thanks,
Suryakiran
Occasional Contributor
Posts: 5

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

Posted in reply to SuryaKiran

Thank you, that will work for me!

Valued Guide
Posts: 597

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

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.

Thanks,
Suryakiran
Solution
‎04-26-2018 05:20 AM
Super User
Super User
Posts: 9,599

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

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.

Occasional Contributor
Posts: 5

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 126 views
  • 0 likes
  • 3 in conversation