With the following code inside a macro, I want to use "P" as a macro variable that assumes multiple values, e.g. P1, P2, ..., which represent a time period each, defined via its start and end, e.g. P1_start and P1_end, P2_start, ... In addition, I want to be able to delay the start and end dates by using a macro variable "lag" that indicates the number of months by which start date and end date should be shifted. This should later be translated into pass-through SQL code. %let P = P1;
%let P1_start = '01JAN2007';
%let P1_end = '30JUN2007';
%let lag = -1;
%let &P._start_l = "%sysfunc(intnx(month, &P1_start.d, &lag., same) , date9.)"d ;
%put P1 start: &P1_start. ;
%put P1 start_l: &P1_start_l. ;
%let SQL = WHERE date_var between &%unquote(&P.)_start_l. and &%unquote(&P.)_end. ;
%put &SQL. ; This code gives: WHERE date_var between "01DEC2006"d and '30JUN2007' I have the following issues: 1. When using single quotes instead of double quotes in the definition of &P1_start_l. in order to make it consistent with the other date, the macro does not resolve in the SQL code: WHERE date_var between '%sysfunc(intnx(month, &P1_start., &lag., same) , date9.)'d and '30JUN2007'd 2. when putting "month" and "same" in double quotes, as indicated here: https://communities.sas.com/t5/SAS-Programming/Error-of-intnx-function-in-a-macro-variable/td-p/416754 the code does not work: "WARNING: Argument 1 to function INTNX referenced by the %SYSFUNC macro function is out of range."; and the SQL code becomes: WHERE date_var between "."d and '30JUN2007'd 3. When trying to make the definition of the lagged date variable with respect to &P., by writing %let &P._start_l = "%sysfunc(intnx(month, &&P._start., &lag., same) , date9.)"d ; The nested macro variable names are not resolving as expected: "Argument 2 to function INTNX referenced by the %SYSFUNC [...] macro function is not a number." My goal is to write a macro that takes the Period &P. and the time lag &lag., and adjusts date &&P._start. (without 'd' at the end) accordingly; so that the final SQL code will look like e.g. for lag = -1: WHERE date_var between '01DEC2006' and '30JUN2007' It is necessary that there is no "d" after the dates in the final SQL code, but if I do not include it in the definition of the lagged start date, I receive an error that there is no number in the argument of INTNX function. How can this be solved?
... View more
