topic running merging code on multiple data at once in SAS Programming

running merging code on multiple data at once

lillymaginta — Thu, 08 Nov 2018 18:39:49 GMT

PROC SQL;
CREATE table fd5
 as select
 one.enrolid as id1,
 k5.enrolid as id2,
 one.age as age1,
 k5.age as age2,
 from one, k5
 where (one.age=k5.age);
quit;

I have the same data for multiple years: data names k5-k15 (10 yrs)

I want to run the above code on each year at once to create the output datasets fd5- fd15

Any help is much appreciated!

Re: running merging code on multiple data at once

PaigeMiller — Thu, 08 Nov 2018 18:57:56 GMT

%macro dothis;
%do yr=5 %to 15;
PROC SQL;
CREATE table fd&yr
 as select
 one.enrolid as id1,
 k&yr.enrolid as id2,
 one.age as age1,
 k&yr.age as age2,
 from one, k&yr
 where (one.age=k&yr.age);
quit;
%end;
%mend;

options mprint;
%dothis

I always point out that its probably not the best idea to put data (the year) into a data set name as you have done here. Better to put the year value into a data set variable, and then the coding becomes much simpler and probably doesn't require any looping and doesn't require any macros. You really ought to go down that path next time.

Re: running merging code on multiple data at once

Reeza — Thu, 08 Nov 2018 18:54:01 GMT

1. Turn it into a macro
https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

2. Combine data sets (append) and do it once instead of 10 times.

3. Change it to a join with an ON instead of cross join to speed things up.

Re: running merging code on multiple data at once

ChrisNZ — Fri, 09 Nov 2018 03:00:03 GMT

Like this?

data FD5
     FD6
     FD7;
 merge ONE(in=A) 
       K5(in=K5 rename=(ENROLID=ID5))
       K6(in=K6 rename=(ENROLID=ID6))
       K7(in=K7 rename=(ENROLID=ID7)); 
 by AGE;
 if A & K5 then output FD5;
 if A & K6 then output FD6;
 if A & K7 then output FD7;
run;