topic Re: Do loop variable is not sequential in SAS Programming

Do loop variable is not sequential

Solph — Thu, 29 Jun 2017 01:07:22 GMT

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;

Re: Do loop variable is not sequential

mkeintz — Thu, 29 Jun 2017 13:01:24 GMT

I'd suggest

Make your upper level=1617, not 16.
make your loop index easier through use of the %BY clause, (i.e. %by 101 as below) <i.e. 1314,1415,1516,...>,
Inside the loop, append a UNION to every iteration but the last
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;

;

Re: Do loop variable is not sequential

Astounding — Thu, 29 Jun 2017 01:58:31 GMT

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.

Re: Do loop variable is not sequential

Solph — Thu, 29 Jun 2017 02:20:16 GMT

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.

Re: Do loop variable is not sequential

Solph — Thu, 29 Jun 2017 02:21:27 GMT

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

Re: Do loop variable is not sequential

Tom — Thu, 29 Jun 2017 03:10:24 GMT

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;

Re: Do loop variable is not sequential

Kurt_Bremser — Thu, 29 Jun 2017 05:59:44 GMT

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;

Re: Do loop variable is not sequential

mkeintz — Thu, 29 Jun 2017 13:00:51 GMT

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.