References to SAS date macros not working due to differing data types

Question

hoping for some help on this one.

Currently, the query uses the below to create references for m1-m6 and d1-d6.

%let m1=1114;
%let d1 ='30NOV2014'd;
%let m2=1214;
%let d2='31DEC2014'd;
%let m3=0115;
%let d3='31JAN2015'd;
%let m4=0215;
%let d4='28FEB2015'd;
%let m5=0315;
%let d5='31MAR2015'd;
%let m6=0415;
%let d6='30APR2015'd;

Based on the rest of the code, the m1-m6 dates must be formatted as mmyy. I have tried to swap the above out with this:

data _datemacro_;
m1 = put(intnx('day','01NOV2014'd,0),mmyyn4.);
call symput('m1',"'"||put(m1,9.)) ;
d1 = put(intnx('day','30NOV2014'd,0),date9.);
call symput('d1',"'"||put(d1,9.)) ;

m2 = put(intnx('day',&d1,+1),mmyyn4.);
call symput('m2',"'"||put(m2,9.)) ;
d2 = put(intnx('month',&d1,+1,'e'),date9.);
call symput('d2',"'"||put(d2,9.)||"'"||"d") ;

...etc through m6 and d6

run;

Below is the rest of the code that yields a garden variety of errors, including

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, DATA, LAST, NULL.

ERROR 200-322: The symbol is not recognized and will be ignored.

proc sql;
create table perf as

select a. field, a. field, a. field, a. reportingdate, 
        a. field, a. field, 
        e. field, 
        f. field

from table a, table2 e, table3 f

    where a. reportingdate between &d1. and &d6.
    and (a. field=1 or a. field=1)  
    and a. field = e. field and a. field = f. field;

quit;

/*Creates performance file by month*/
%macro month (mon,date);
data m&mon. (rename=(field=active&mon. field=co&mon. field=es&mon. field=sr&mon.));
set perf;
where datepart(reportingdate)=&date.;
run;

proc sort data=m&mon.; by field descending co&mon.; run;
proc sort data=m&mon. nodupkey out=m2&mon.; by field; run;
%mend month;


%month (&m1.,&d1.);
%month (&m2.,&d2.);
%month (&m3.,&d3.);
%month (&m4.,&d4.);
%month (&m5.,&d5.);
%month (&m6.,&d6.);

I am able to get it to run accurately until the last 6 lines, where it comes up with 78 errors just from running those 6 lines.

Any suggestions on how to write the macro to keep the correct data type while accurately defining the month start and end dates? When I try and change the start date and end date of each month to the same format, something within the rest of the code causes an error stating that it cannot work with two variables of different formats, even when they are clearly the same format as defined in the code.

Please let me know if there is anything I can clarify, as this was a little harder to explain than I intended.

Thank you for your help.

Answer 1

So you're basically trying to build a macro that will run a report month-by-month. I think using a macro is a good idea, but your structure could benefit from a re-org.

The first thing to fix is the hardcoded dates. Hardcoding is bad 99% of the time. Why not use a loop instead?

Initialise the start and end dates at the top of your program. In future they're easy to find and change if they're at the top, and you won't need to search through your code trying to figure out what else needs to change:

* PICK ANY DATES IN THE MONTHS YOU WANT TO START AND END.  IE. DOESNT MATTER IF YOU CHOOSE THE FIRST OR THE 20TH. IT WILL RUN FOR THAT MONTH;
%let rpt_start = %sysfunc(mdy(11,1,2014));
%let rpt_end   = %sysfunc(mdy( 4,1,2015));

Go and get the data between the start and end dates:

proc sql;
create table perf as

select a. field, a. field, a. field, a. reportingdate, 
        a. field, a. field, 
        e. field, 
        f. field

from table a, table2 e, table3 f

    where a. reportingdate between &rpt_start and &rpt_end
    and (a. field=1 or a. field=1)  
    and a. field = e. field and a. field = f. field;

quit;

Now loop over each month inbetween the start and end dates. Create the desired datasets as we go.

%macro create_monthly_datasets;
    %local tmp_dt tmp_end rpt_dt;

    %let tmp_end = %sysfunc(intnx(month,&rpt_end,0,end)); *CALC END-DATE DESIRED;

    %let tmp_dt = %sysfunc(intnx(month,&rpt_start,0,beginning)); *INITIALISE LOOP;
    %do %while (&tmp_dt le &tmp_end);

      * CALC ACUTAL DATE WANTED AND STORE IT IN RPT_DT;
      * CALC THE MMYY VAL YOU NEED;
      * PRINT OUT BOTH VALUES TO MAKE SURE THEYRE CORRECT;

      %let rpt_dt = %sysfunc(intnx(month,&tmp_dt,0,end)); 
      %let mmyy   = %sysfunc(month(&rpt_dt),z2.)%substr(%sysfunc(year(&rpt_dt)),3,2);
      %put %sysfunc(sum(&rpt_dt),date9.) &mmyy;  

      * DO THE WORK;
      data m&mmyy (rename=(field=active&mmyy field=co&mmyy field=es&mmyy field=sr&mmyy));
        set perf;
        where datepart(reportingdate)=&rpt_dt;
      run;

      proc sort data=m&mmyy; by field descending co&mmyy; run;
      proc sort data=m&mmyy nodupkey out=m2&mmyy; by field; run;

      %let tmp_dt = %sysfunc(intnx(month,&tmp_dt,1,beginning)); * ITERATE LOOP;
    %end;


%mend;
%create_monthly_datasets;

Answer 2

Try the following for creating the macro variables:

data _null_;
  START_MTH = '01nov2014'd;
  do i = 1 to 6;
    T_DATE = intnx('month',START_MTH,i)-1; /*Shift forwards i months then back 1 day*/
    call symput(cats('m',i),put(T_DATE,mmyyn4.));
    call symput(cats('d',i),cats("'",put(T_DATE,date9.),"'d"));
  end;
run;