SAS %DO Loop to retrieve multiple Datasets

1
votes

I'm trying to use a %DO loop to append multiple monthly data files. Here's the code I have so far:

options mprint symbolgen source mlogic merror syntaxcheck ;


%MACRO INT;

%DO i=01 %TO 03 %BY 1;

libname appd "Qual1.Qual2.Qual3.QUAL416&i." disp=shr;

data work&i.;
set appd.file;
run;

data final;
set work;
run;

proc append data=work&i.
            base=final;
run;

%MEND;

%INT;

I'm getting an error :

WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks.

I've never received this error before, and I'm not entirely sure what it means. I'm trying to create this macro to append a few files from this year, but ideally it would span across multiple years. i.e. changing mtro16&i. to mtro&i. EXCEPT I don't want to have to code %DO i=1601 %TO 1612 %BY 1 explicitly, because I will have to change it for every year, e.g. if I begin in 2010, i'll have to code 2010, then a separate %DO statement for 2011, etc. all the way until 2016. This doesn't sound like much, but across a decade >+ it can be.

UPDATE: I changed my code to include this bit: 

`%DO i=1005 %TO 1607;
%IF i=1013 %THEN %DO i=1101 %TO 1112;
/*%ELSE %IF i=1113 %THEN %DO i=1201 %TO 1212;*/
/*%ELSE %IF i=1213 %THEN %DO i=1301 %TO 1312;*/
/*%ELSE %IF i=1313 %THEN %DO i=1401 %TO 1412;*/
/*%ELSE %IF i=1413 %THEN %DO i= 1501 %TO 1512;*/
/*%ELSE %IF i=1513 %THEN %DO i=1601 %TO 1607;*/`

It's kind of janky, but I thought it would work to only loop to the end of the year and then begin the next iteration after i=1012 to i=1101. However, this is what is happening in my log:

 MLOGIC(INT):  %DO loop index variable I is now 1012; loop will iterate again. 
 MLOGIC(INT):  %IF condition i=1013 is FALSE 
 MLOGIC(INT):  %DO loop index variable I is now 1013; loop will iterate again. 
 MLOGIC(INT):  %IF condition i=1013 is FALSE

How is SAS treating this %IF condition? It tells me that variable i = 1013 and that %IF condition i=1013 is FALSE? Why is this not TRUE?

3
loopsmacrossassas-macro
I usually receive this kind of warning when creating a macro variable that resolves to a string and the string itself has more than the specified characters. It can be perfectly valid and work fine but SAS thinks there might be a problem as it's not designed to expect strings as long as that. If the code does as expected I wouldn't worry about it as I don't think you can stop it from generating the warning. That said, you don't have a %end; to close your loop so it it just concatenating them all together? – Matt_Roberts
SMH. I miss these all the time – DukeLuke
Also, the variable 01 in your do statement will resolve to 1 in the macro when called. so you will end up with mtro161 through mtro163. You will need to add something like an if length i < 2 then do j = strip(0||i) to concatenate the leading zero i suspect and then make your libname mtro16&j. instead. – Matt_Roberts
So how could i reformat this to do 1001 - 1607? or any give length of time where my do loop resolves from 1001-1012, then 1101-1112 and so on, without having separate data steps or %DO loops – DukeLuke
It is hard to follow the logic of your macro since you are making datasets you do not reference and referencing datasets you do not create. Are you appending to an existing dataset or creating a new one by combining the list? If the latter then just use SET WORK01 - WORK03; – Tom

3 Answers

3
votes

I think when working with dates, you should always actually work with date values rather than trying to shortcut things by using years or months or other such 'shortcuts'. In my experience, these always lead to confusing and or buggy code.

Below is a macro that shows how you can pass in a start date and an end date (that don't even have to be the 1st of the month) and it will return the appropriate monthly names. You can then adapt this to work with your proc append or other code as desired.

It actually takes 2 date values as parameters. It then uses a %do %while loop and the intnx() function to loop from the start date to the end date 1 month at a time. I use %sysfunc(sum(),yymmn4.) to calculate the desired format for your monthly datasets and print it to the log.

Code:

%macro append_monthly(iStart_date=, iEnd_date=);

  %local tmp_date id;
  %let tmp_date = %sysfunc(intnx(month,&iStart_date,0,beginning)) ;

  %do %while (&tmp_date le &iEnd_date);

    %let id = %sysfunc(sum(&tmp_date),yymmn4.);
    %put &=id;

    %let tmp_date = %sysfunc(intnx(month,&tmp_date,1,beginning)) ;

  %end;

%mend;
%append_monthly(iStart_date=%sysfunc(mdy(1,1,2010)),  iEnd_date=%sysfunc(mdy(1,1,2013)) );

Note that the %sysfunc(sum(&tmp_date),yymmn4.) is kind of a shortcut for formatting a number when using macros. %sysfunc() requires us to provide a function as the first parameter, and you can optionally provide a format as the second parameter. The sum() function is convenient because it leaves the numeric value unchanged.

Output:

ID=1001
ID=1002
ID=1003
...
ID=1011
ID=1012
ID=1101
ID=1102
ID=1103
...
ID=1110
ID=1111
ID=1112
ID=1201
ID=1202
ID=1203
...
ID=1210
ID=1211
ID=1212
ID=1301
2
votes

Here's one way of doing such a loop:

%macro month_loop(MTH_FROM, MTH_TO);
  %local T_MTH_FROM T_MTH_TO MTH_DIFF CUR_MTH MTH_OFFSET;
  %let T_MTH_FROM = %sysfunc(inputn(&MTH_FROM,yymmn.));
  %let T_MTH_TO   = %sysfunc(inputn(&MTH_TO,yymmn.));
  %let MTH_DIFF   = %sysfunc(intck(month,&T_MTH_FROM,&T_MTH_TO));

  %do MTH_OFFSET = 0 %to &MTH_DIFF;
    %let CUR_MTH = %sysfunc(intnx(month,&T_MTH_FROM,&MTH_OFFSET), yymmn4.);
    %put CUR_MTH = &CUR_MTH;
  %end;
%mend;

%month_loop(1001,1607);
0
votes

It is a little hard to follow your logic, but it looks like you just want to do:

data final;
  set appd.file work01-work03 ;
run;