DATA Step, Macro, Functions and more

Do loop variable is not sequential

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Do loop variable is not sequential

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;


Accepted Solutions
Solution
‎07-07-2017 11:44 PM
Valued Guide
Posts: 797

Re: Do loop variable is not sequential

[ Edited ]

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;

   ;

 

 

 

View solution in original post


All Replies
Solution
‎07-07-2017 11:44 PM
Valued Guide
Posts: 797

Re: Do loop variable is not sequential

[ Edited ]

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;

   ;

 

 

 

Frequent Contributor
Posts: 101

Re: Do loop variable is not sequential

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.

 

Valued Guide
Posts: 797

Re: Do loop variable is not sequential

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.

Super User
Posts: 5,081

Re: Do loop variable is not sequential

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.

Frequent Contributor
Posts: 101

Re: Do loop variable is not sequential

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

Super User
Posts: 6,935

Re: Do loop variable is not sequential

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,499

Re: Do loop variable is not sequential

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 135 views
  • 2 likes
  • 5 in conversation