topic Re: macro do loop to append monthly dataset in SAS Programming

macro do loop to append monthly dataset

kevsma — Tue, 10 Jan 2023 19:41:04 GMT

Hello,

I am struggling with coming up with the proper do loop to append 12 monthly datasets. I have my begin and end date set up using the macros, would appreciate if someone can help!!

Below are my begin and end month macros:

%let moe=%sysfunc(intnx(month,%sysfunc(today()),-5,same), yymmn.); /*go back 6 mo, begin month*/
%let mob=%sysfunc(intnx(month,%sysfunc(today()),-16,same), yymmn.); /*go back 17 mo, end month*/

%put &mob.;
%put &moe.;

for example, the above macros returns mob to be: 2021 Aug and moe to be: 2022 Jul with date format: 202109 to 202208

All I need is to append datasets between the two dates above by 1 month interval, namely:

cm202109

cm202110

cm202111

cm202112

cm202201 ...

Thanks!!

Re: macro do loop to append monthly dataset

Tom — Tue, 10 Jan 2023 19:49:57 GMT

To set the start and end use actual dates and not those abbreviations.

%let mob=%sysfunc(intnx(month,%sysfunc(today()),-16,B));
%let moe=%sysfunc(intnx(month,%sysfunc(today()),-5,E));

Generate the abbreviations only when you need to build the dataset names.

data want ;
  set 
%do offset=0 %to %sysfunc(intck(month,&mob,&moe));
    cm%sysfunc(intnx(month,&mob,&offset),yymmn6.)
%end;
  ;
run;

Re: macro do loop to append monthly dataset

kevsma — Tue, 10 Jan 2023 20:04:28 GMT

Thanks Tom for your quick response!

I plugged in your code and SAS spit out error saying "do loop is not valid in open code", please see below:

    data base;
27           set %do offset=0 %to %sysfunc(intck(month,&mob.,&moe.));
ERROR: The %DO statement is not valid in open code.
28             lcmf.cm%sysfunc(intnx(month,&mob.,&offset),yymmn6.)
WARNING: Apparent symbolic reference OFFSET not resolved.
WARNING: Apparent symbolic reference OFFSET not resolved.
ERROR: Argument 3 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list.  Execution of %SYSCALL statement or %SYSFUNC 
       or %QSYSFUNC function reference is terminated.
29         %end;
ERROR: The %END statement is not valid in open code.
NOTE 139-205: Line generated by the macro function "SYSFUNC".
28              lcmf.cm.
                ________
                22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, 
              NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.  

NOTE: Line generated by the macro function "SYSFUNC".
28              lcmf.cm.
                ________
                201
ERROR 201-322: The option is not recognized and will be ignored.

30           ;
31         run;

and my code is as below, I only added the libname prefix "lcmf"

%let mob=%sysfunc(intnx(month,%sysfunc(today()),-16,B));
%let moe=%sysfunc(intnx(month,%sysfunc(today()),-5,E)); 

%put &mob.;
%put &moe.;

data base;
  set %do offset=0 %to %sysfunc(intck(month,&mob.,&moe.));
    lcmf.cm%sysfunc(intnx(month,&mob.,&offset),yymmn6.)
%end;
  ;
run;

Any idea of what may go wrong?

Thanks again!

Re: macro do loop to append monthly dataset

Tom — Tue, 10 Jan 2023 21:35:10 GMT

To use that %DO loop you need to create a macro and then call the macro.

In that case it might be best to make a macro takes the start and stop dates as inputs and just spits out the list of dataset names.

%macro dslist(start,stop);
%local offset;
%do offset=0 %to %sysfunc(intck(month,&start.,&stop.));
  lcmf.cm%sysfunc(intnx(month,&start.,&offset),yymmn6.)
%end;
%mend dslist;

%let mob=%sysfunc(intnx(month,%sysfunc(today()),-16,B));
%let moe=%sysfunc(intnx(month,%sysfunc(today()),-5,E)); 
data want;
  set %dslist(&mob,&moe) ;
run;

Re: macro do loop to append monthly dataset

ballardw — Tue, 10 Jan 2023 21:56:50 GMT

If you want to create something like the following then no macro code is needed.

data want;
set
cm202109
cm202110
cm202111
cm202112
cm202201
cm202202
cm202203
cm202204
cm202205
cm202206
cm202207
;
run;

CALL Execute places strings into a execution buffer to start after the end of the data step writing them.

data _null_;
   call execute('data want; set');
   date = intnx('month',today(),-16,'b');
   do until ( date=intnx('month',today(),-5,'b') );
      str=cats('cm',put(date,yymmn.));
      call execute (str );
      date = intnx('month',date,1,'b');
   end;
   /* end the data step. first ; ends the SET statement
   second ends the run*/
   call execute (';  run;');
run;

Text in quotes is literal. Variables resolve the value when written to the buffer.

Re: macro do loop to append monthly dataset

Ksharp — Wed, 11 Jan 2023 11:44:14 GMT

/*
If you just want append these tables,
that would be easy.
*/

data 
cm202109
cm202110
cm202111
cm202112
cm202201
cm202202
cm202203
cm202204
cm202205
cm202206
cm202207
;
set sashelp.class;
run;


options nodsnferr;
data want;
 set cm202109-cm202207;
run;

Re: macro do loop to append monthly dataset

kevsma — Fri, 27 Jan 2023 17:15:46 GMT

Sorry for my delayed response on this last thread, and thank you so much, Tom! This works like a magic.