sas macros for incrementing date

Question

My codes are:

libname " Cp/mydata"
options ;
%let yyyymmdd=20050210;
%let offset=0;
%let startrange=0;
%let endrange=0;

/* MACRO FOR INCREMENTING THE DATE */
%macro base(yyyymmdd=, offset=);
%local date x ds; /* declare macro variables with local scope */
%let date=%sysfunc(mdy(%substr(&yyyymmdd,5,2)
                   ,%substr(&yyyymmdd,7,2)
                   ,%substr(&yyyymmdd,1,4))); /* convert yyyymmdd to SAS date */
%let loopout=100;/* hardcoded - number of times to check whether ds exists */
%do x=&offset %to &loopout; /* begin loop */
   /* convert &date to yyyymmdd format */
   %let ds=AQ.CO_%sysfunc(intnx(day,&date,&offset),yymmddn8.); 
   %if %sysfunc(exist( &ds )) %then %do;
      %put &ds exists!;
      &ds /* write out the dataset, if it exists */
      %let x=&loopout; /* exit loop */
   %end;
   %else %do;
      %put &ds does not exist - checking subsequent day;
      %let date=&date+1;
   %end;
%end;
%mend;

%macro loop(yyyymmdd=, startrange=, endrange=);
%local date x ds;
%let date=%sysfunc(mdy(%substr(&yyyymmdd,5,2)
                      ,%substr(&yyyymmdd,7,2)
                      ,%substr(&yyyymmdd,1,4)));
data x;
set set %base(yyyymmdd=&yyyymmdd, offset=0)
/* loop through each specific dataset, checking first whether it exists.. */
%do x=&startrange %to &endrange;
   %let ds=AQ.CO_%sysfunc(intnx(day,&date,&x),yymmddn8.);
   %if %sysfunc(exist( &ds )) %then %do;
      &ds
   %end;
%end;
;
run;
%mend;

This was the error generated when I tried to run this macro.

data temp;

58 set %loop(yyyymmdd=&yyyymmdd, startrange=&startrange, 58 ! endrange=&endrange);

ERROR: File WORK.DATA.DATA does not exist.

ERROR: File WORK.X.DATA does not exist.

AQ.CO_20050210 does not exist - checking subsequent day

AQ.CO_20050211 does not exist - checking subsequent day

AQ.CO_20050212 exists!

NOTE: The system stopped processing this step because of errors.

I want help on two things:

1) Here, I'm trying to increment my date by 1 or 2 or so on if that date is not there in my original dataset. Please help to make this macro work fine.

2)I would like to have another column ie work.date in my data that will have 0 or 1(1 if the specified date yyyymmdd exist in our original data and 0 if I'm incrementing). Please make the specified changes in my macro. Thanks in advance!!

Robert Penridge Robert Penridge · Accepted Answer · 2014-02-24T20:16:27

I wasn't quite sure exactly what your %base() macro was trying to achieve but there were a couple of things I noticed.

First try turning on option mprint; to help with debugging. If you still need more debugging info you can also try turning on the following options (I'd suggest 1 at a time until you know which ones you need):

option symbolgen macrogen mlogic;

Secondly, you have set set instead of just set in your example code. I don't think that is helping any =).

When I tried the code quickly on my machine I noticed I was getting a strange error (different from yours) when I called the %base() macro. It seemed like an error that shouldn't be occurring so I wrapped the call in an %unquote() function just to make sure and I started to receive the error your post mentioned. You may want to try this as well:

set %unquote(%base(yyyymmdd=&yyyymmdd, offset=0))

Normally the %unquote() function isn't required unless you are explicitly using macro quoting functions and getting strange errors, but SAS macros sometimes seem to have a mind of their own. I only ever add this when I know it is required.

Also, your libname call is missing a semicolon at the end of the line.

Finally, some advice on working with dates in the SAS macro language. Don't keep converting between the date value, and the formatted value. It will make your code bigger, more error prone and more difficult to read. I know because I used to do it that way too. Try instead to always work with variables that contain the actual date value (by using the result from %sysfunc(mdy()) ) and then if you need a formatted value then create a new variable (eg. %let yyyymmdd = %sysfunc(putn(&mydate),yymmddn8.);. When you pass values from one macro to another, don't pass the formatted values even if it seems easier, pass the actual values.

Making the above changes removed all errors on my machine. Final code:

libname " Cp/mydata";

%let yyyymmdd=20050210;
%let offset=0;
%let startrange=0;
%let endrange=0;

/* MACRO FOR INCREMENTING THE DATE */
%macro base(yyyymmdd=, offset=);
%local date x ds; /* declare macro variables with local scope */
%let date=%sysfunc(mdy(%substr(&yyyymmdd,5,2)
                   ,%substr(&yyyymmdd,7,2)
                   ,%substr(&yyyymmdd,1,4))); /* convert yyyymmdd to SAS date */
%let loopout=100;/* hardcoded - number of times to check whether ds exists */
%do x=&offset %to &loopout; /* begin loop */
   /* convert &date to yyyymmdd format */
   %let ds=AQ.CO_%sysfunc(intnx(day,&date,&offset),yymmddn8.); 
   %if %sysfunc(exist( &ds )) %then %do;
      %put &ds exists!;
      &ds /* write out the dataset, if it exists */
      %let x=&loopout; /* exit loop */
   %end;
   %else %do;
      %put &ds does not exist - checking subsequent day;
      %let date=&date+1;
   %end;
%end;
%mend;

%macro loop(yyyymmdd=, startrange=, endrange=);
%local date x ds;
%let date=%sysfunc(mdy(%substr(&yyyymmdd,5,2)
                      ,%substr(&yyyymmdd,7,2)
                      ,%substr(&yyyymmdd,1,4)));
data x;
set %unquote( %base(yyyymmdd=&yyyymmdd, offset=0))
/* loop through each specific dataset, checking first whether it exists.. */
%do x=&startrange %to &endrange;
   %let ds=AQ.CO_%sysfunc(intnx(day,&date,&x),yymmddn8.);
   %if %sysfunc(exist( &ds )) %then %do;
      &ds
   %end;
%end;
;
run;
%mend; 
%loop(yyyymmdd=&yyyymmdd, startrange=&startrange, endrange=&endrange);

sas macros for incrementing date

2 Answers