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!
%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.
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.
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;
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.