BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
kevsma
Quartz | Level 8

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!!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

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;
kevsma
Quartz | Level 8

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!

Tom
Super User Tom
Super User

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;

 

kevsma
Quartz | Level 8

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

ballardw
Super User

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.

Ksharp
Super User
/*
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 1461 views
  • 2 likes
  • 4 in conversation