Hi,
I've many years of data I need to combine using SQL specifically. The data names are not in numerical order (e.g. 2011, 2012, 2013.. 2016.). Instead its in the year format of 1112, 1213, 1314, ....1516, 1617. My first year of data in the example is 2013 and last year of data is 2016.
Below is the 4 datsets and a code that I'd spell out for each year. The do loop marcro I wrote didn't work. Can you point the errors for me? Much appreciated.
data havedata1314Q4; input id date;
datalines;
1 2005
2 2006
;
data havedata1415Q4; input id date;
datalines;
1 2005
4 2010
;data havedata1516Q4; input id date;
datalines;
2 2006
6 2012
;data havedata1617Q4; input id date;
datalines;
7 2013
8 2015
;
*Code 1 - spelling out steps;
%let lastyr2=16; *16 as latest year of data - 2016;
%let lastyr3= %sysfunc(putn(&lastyr2,z2.))%sysfunc(putn(%eval(&lastyr2+1),z2.)); *eg yr=1617;
proc sql; create table combined as
select distinct ID, Date from HaveData1314Q4
union
select distinct ID, Date from HaveData1415Q4
union
select distinct ID, Date from HaveData1516Q4
union
select distinct ID, Date from HaveData&lastyr3.Q4;
create table want as
select distinct ID, date
from combined;
quit;
*Code 2 using do loop;
%let lastyr2=16; *16 as latest year of data - 2016;
%let lastyr3= %sysfunc(putn(&lastyr2,z2.))%sysfunc(putn(%eval(&lastyr2+1),z2.)); *eg yr=1617;
%let prevyr=20%sysfunc(putn(%eval(&lastyr2-1),z2.)); *get prevoius year 2015;
%macro get;
proc sql; create table out1 as
%do year=2013 %to &prevyr;
%let year1= %sysfunc(putn(&year.,z2.))%sysfunc(putn(%eval(&year.+1),z2.));
select distinct ID, date from HaveData&year1.Q4
union
%end;
select distinct ID, date from HaveData&lastyr3.Q4
create table want as
select distinct ID, date
from combined;
quit;
%mend get;
%get;
I'd suggest
%let last_YYnextYY=1617;
create table want as
%do YYnextYY = 1213 %to &last_YYnextYY %by 101;
select id, date from have&yynextYY.Q4
%if &YYnextYY NE &last_YYnextYY %then union;
%end;
;
I'd suggest
%let last_YYnextYY=1617;
create table want as
%do YYnextYY = 1213 %to &last_YYnextYY %by 101;
select id, date from have&yynextYY.Q4
%if &YYnextYY NE &last_YYnextYY %then union;
%end;
;
Thanks. Here is the code
%let last_YYnextYY=1617;
%macro get;
proc sql;
create table want as
%do YYnextYY = 1314 %to &last_YYnextYY %by 101;
select id, date from have&yynextYY.Q4
%if &YYnextYY=&last_YYnextYY %then union;
%end;
run;
%mend get;
%get;
And it got an errorr message. It looks like the union one is not placed right?
Error message: create table want as select id, date from have1314Q4 select id, date from have1415Q4 select id, date from have1516Q4 select id, date from have1617Q4 union run;
Any tips? Sorry it's beyond my skills. Thanks.
Oops. I didn't transcribe my program to the forum properly. Instead of
%if &YYnextYY=&last_YYnextYY %then union;
it should have been
%if &YYnextYY NE &last_YYnextYY %then union;
I will edit the original note.
First error: expecting us to understand what you mean when you say it didn't work. You must have gotten some sort of error message that you could share.
Instead of trying to fix the code, simplify it. You could use a %do loop that reads:
%do y=11 to 16;
Using &Y, it should be easy to get the numerical pieces that you need. For example, when &Y is 11 ...
To get 2011: %eval(&y + 2000)
To get 1112: &y%eval(&y+1)
Take out the complications.
Thanks for the advice. I knew it was clumsy. I''ll take note and modify.
You can take @Tom's code and use call execute to streamline it even more:
data _null_;
call execute('data want; set ');
do yr=13 to 16 ;
call execute(cats('HaveData',put(yr,z2.),put(yr+1,z2.),'Q4') !! ' ');
end;
call execute('; keep id date; run;');
run;
First simplify the code you need to do the job. Using SQL is just going to make the problem harder and also you could run into limits that you will not have when using regular SAS code.
data want ;
set HaveData1314Q4
HaveData1415Q4
HaveData1516Q4
HaveData1617Q4
;
keep id date;
run;
proc sort nodupkey;
by id date;
run;
Now to generate the list you could use macro code, but you could also just use normal code and store the generated list into a macro varaible.
data _null_;
length str $300;
do yr=13 to 16 ;
str=catx(' ',str,cats('HaveData',put(yr,z2.),put(yr+1,z2.),'Q4'));
end;
call symputx('dslist',str);
run;
data want ;
set &dslist ;
keep id date;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.