Hi,
it's a bunch of questions in a single thread so I'll answer them pragmatically
Hope it helps
@Max11 wrote:
WHERE date_var between "01DEC2006"d and '30JUN2007'
NB: Make sure you compare variables of same type, either
" WHERE date_var between "01DEC2006"d and '30JUN2007'd"
or like you wrote at the end:
WHERE date_var between '01DEC2006' and '30JUN2007'
@Max11 wrote:
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:
SAS macro variables do not resolve between single quotes unless you use this trick
@Max11 wrote: 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:
The arguments of sysfunc calls do not need to be quoted.
If you want the date9 date use:
%let &P._start_l= %sysfunc(putn(%sysfunc(intnx(month, &P1_start.d, &lag., same)),date9.)) ;
If you want the digits use:
%let &P._start_l = %sysfunc(intnx(month, &P1_start.d, &lag., same)) ;
@Max11 wrote:
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."
To get the date9 use:
%let &P._start_l = %sysfunc(putn(%sysfunc(intnx(month, &&&P._start.d, &lag., same)),date9.)) ;
To get the digit use: %let &P._start_l = %sysfunc(intnx(month, &&&P._start.d, &lag., same)) ;
%put &&&P._start_l.;
@Max11 wrote:
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?
Use this:
%let P = P1;
%let P1_start = '01JAN2007';
%let P1_end = '30JUN2007';
%let lag = -1;
%let &P._start_l = %sysfunc(putn(%sysfunc(intnx(month, &&&P._start.d, -1, same)),date9.)) ;
%put &&&P._start_l.;
%let want=where date_var between %str(%')&&&P._start_l.%str(%') and &&&P._end;
%put &=want;
... View more
