BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DeepaG
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
SuryaKiran
Meteorite | Level 14

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
DeepaG
Calcite | Level 5

Thank you, that will work for me!

SuryaKiran
Meteorite | Level 14

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DeepaG
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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