I cannot for the life of me figure out how to get these formats correct for a usable date macro. I have two pieces of information that I want to use to create date macro variables. The first is a year and the second is a tag to let me know whether I create the variables for the given year or the previous year. For instance, if I set year to 2025 and period to PREV, I want to create the date variables for 2024. The dates I want are the start of the year, end of the year, 6 months before the start, and 6 months after the end. I am looking for them all to be of the form 01JAN2024.
I have been crawling forums and the SAS help site and have tried a thousand combinations. I can't find anything that works and I don't even know how to properly print the results to check. Here is what I've tried most recently:
%let year = 2025;
%let period = PREV;
%let start_date = %sysfunc(mdy(1, 1, &meas_yr));
%macro set_dates;
%if &period = PREV
%then %do;
%let p_start = %sysfunc(intnx(year, &start_date, -1, beg));
%let p_end = %sysfunc(intnx(year, &start_date, -1, end));
%let period_start = %sysfunc(putn(&p_start, date9.));
%let period_end = %sysfunc(putn(&p_end, date9.));
%end;
%else %do;
%let p_start = %sysfunc(intnx(year, &start_date, 0, beg));
%let p_end = %sysfunc(intnx(year, &start_date, 0, end));
%let period_start = %sysfunc(putn(&p_start, date9.));
%let period_end = %sysfunc(putn(&p_end, date9.));
%end;
%mend set_dates;
%set_dates;
%let period_lkbk = %sysfunc(intnx(month, &p_start., -6, beg), date9.);
%put &period_lkbk;
%let period_lkfw = %sysfunc(intnx(month, &p_end., 6, end), date9.);
%put &period_lkfw;
The following is my code to test what is being set:
%put Date_Test_1 : %sysfunc(putn(&p_start, date9.));
%put Date_Test_2 : &period_start;
%put Date_Test_3 : %sysfunc(putn(&p_end, date9.));
%put Date_Test_4 : &period_end;
%put Date_Test_5 : &period_lkbk;
And the results I get are:
SYMBOLGEN: Macro variable P_START resolves to 23376
27
28 %put Date_Test_1 : %sysfunc(putn(&p_start, date9.));
Date_Test_1 : 01JAN2024
29 %put Date_Test_2 : &period_start;
SYMBOLGEN: Macro variable PERIOD_START resolves to 01JAN2024
Date_Test_2 : 01JAN2024
30 %put Date_Test_3 : %sysfunc(putn(&p_end, date9.));
WARNING: Apparent symbolic reference P_END not resolved.
WARNING: Apparent symbolic reference P_END not resolved.
ERROR: Argument 1 to function PUTN 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.
Date_Test_3 :
31 %put Date_Test_4 : &period_end;
SYMBOLGEN: Macro variable PERIOD_END resolves to 31DEC2024
Date_Test_4 : 31DEC2024
32 %put Date_Test_5 : &period_lkbk;
SYMBOLGEN: Macro variable PERIOD_LKBK resolves to 01JUL2023
Date_Test_5 : 01JUL2023
33 %put Date_Test_6 : &period_lkfw;
SYMBOLGEN: Macro variable PERIOD_LKFW resolves to .
Date_Test_6 : .
Eventually I want to use this in a statement within proc sql like this:
WHERE startdate >= "&period_lkbk."d and enddate <= "&period_lkfw."d;
Why is p_end not resolving? I feel like I'm close but can't get to the finish line.
Your macro assigns a value to PERIOD_END (and P_START etc ) but there is no code that would have made sure that PERIOD_END was defined outside of the macro, unlike START_DATE where there is a %LET statement in open code to create it in the GLOBAL macro scope before you started running the macro. Since the macro variables do not yet exist the %LET statements inside of a macro definition will make LOCAL macro variables, which will disappear when the macro ends.
Since there is nothing in the code that needs a macro you could just try eliminating the macro definition.
%let year = 2025;
%let period = PREV;
%let start_date = %sysfunc(mdy(1, 1, &year));
%if &period = PREV %then %do;
%let p_start = %sysfunc(intnx(year, &start_date, -1, beg));
%let p_end = %sysfunc(intnx(year, &start_date, -1, end));
%let period_start = %sysfunc(putn(&p_start, date9.));
%let period_end = %sysfunc(putn(&p_end, date9.));
%end;
%else %do;
%let p_start = %sysfunc(intnx(year, &start_date, 0, beg));
%let p_end = %sysfunc(intnx(year, &start_date, 0, end));
%let period_start = %sysfunc(putn(&p_start, date9.));
%let period_end = %sysfunc(putn(&p_end, date9.));
%end;
%put &=year &=period &=start_date ;
%put &=p_start &=p_end &=period_start &=period_end;
%let period_lkbk = %sysfunc(intnx(month, &p_start., -6, beg), date9.);
%put &=period_lkbk;
%let period_lkfw = %sysfunc(intnx(month, &p_end., 6, end), date9.);
%put &=period_lkfw;
Results
YEAR=2025 PERIOD=PREV START_DATE=23742
P_START=23376 P_END=23741 PERIOD_START=01JAN2024 PERIOD_END=31DEC2024
PERIOD_LKBK=01JUL2023
PERIOD_LKFW=30JUN2025
Hint:
To make your macro programming easier to understand and maintain do not use "magic" macro variables inside your macro definition. By that I mean you need to be purposeful in designing whether the macro variables you reference (and those you create) will be LOCAL to the macro (so that the disappear when the macro has finished running) or EXTERNAL to the macro (so that they remain after the macro has finished). You do not even have any comments that would help the next user (which will probably by you) understand what your plan was.
So perhaps you want a macro like this that takes two inputs and generates 5 GLOBAL macro variables.
%macro set_dates(year,period);
%global start_date p_start p_end period_start period_end ;
%let start_date = %sysfunc(mdy(1, 1, &year));
%if &period = PREV %then %do;
%let p_start = %sysfunc(intnx(year, &start_date, -1, beg));
%let p_end = %sysfunc(intnx(year, &start_date, -1, end));
%let period_start = %sysfunc(putn(&p_start, date9.));
%let period_end = %sysfunc(putn(&p_end, date9.));
%end;
%else %do;
%let p_start = %sysfunc(intnx(year, &start_date, 0, beg));
%let p_end = %sysfunc(intnx(year, &start_date, 0, end));
%let period_start = %sysfunc(putn(&p_start, date9.));
%let period_end = %sysfunc(putn(&p_end, date9.));
%end;
%mend set_dates;
Which you can then use like this:
%set_dates(year=2025,period=PREV);
%put &=start_date %put &=p_start &=p_end &=period_start &=period_end;