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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

I'd suggest

  1. Make your upper level=1617,  not 16.
  2. make your loop index easier through use of the %BY clause, (i.e. %by 101 as below)  <i.e. 1314,1415,1516,...>,
  3. Inside the loop, append a UNION to every iteration but the last
  4. I think you can drop the DISTINCT modifier, because UNION apparently will remove duplicates

 

 

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

   ;

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

7 REPLIES 7
mkeintz
PROC Star

I'd suggest

  1. Make your upper level=1617,  not 16.
  2. make your loop index easier through use of the %BY clause, (i.e. %by 101 as below)  <i.e. 1314,1415,1516,...>,
  3. Inside the loop, append a UNION to every iteration but the last
  4. I think you can drop the DISTINCT modifier, because UNION apparently will remove duplicates

 

 

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

   ;

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Solph
Pyrite | Level 9

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.

 

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Astounding
PROC Star

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.

Solph
Pyrite | Level 9

Thanks for the advice. I knew it was clumsy. I''ll take note and modify.

Kurt_Bremser
Super User

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;
Tom
Super User Tom
Super User

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;

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
  • 7 replies
  • 1874 views
  • 2 likes
  • 5 in conversation