Resolve macro variable name in intnx function to generate SQL code

[Max11]

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/4167...
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?

[Oligolas]

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/4167... 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;

---

---

[Patrick]

I'd be using a data _null_ step to create and populate these macro variables. May-be below gets you over the hurdle

%let P = P1;
%let P1_start = 01JAN2007;
%let lag = -1;

%let &P._start_l = %tslit(%sysfunc(intnx(month, "&P1_start"d, &lag., same) , date9.))d ;
%put &&&P._start_l;

34         %put &&&P._start_l;
'01DEC2006'd

[Oligolas]

@Patrick & @PaigeMiller Choose the approach you desire, but SQL pass through code is requested so it's 

WHERE date_var between '01DEC2006' and '30JUN2007'

[PaigeMiller]

I think there are much easier approaches, that don't involve whether or not to use single quotes or double quotes or %UNQUOTE, so let me explain.

I think it is always better to work with dates as valid numeric SAS date values, rather than as character strings. This generally avoids the needs for single quotes and double quotes and %UNQUOTE. There are other reason to work with valid numeric SAS date values as well. And finally at the end of the program when human readable dates are needed, such as 30JUN2007, you format the date so it appears properly and in this case, add quotes around the date as required.

In this case:

%let P = P1;
/* Convert start date and end date to valid numeric SAS date values */
%let P1_start = %sysevalf('01JAN2007'd);
%let P1_end   = %sysevalf('30JUN2007'd);
%put &=p1_start &=p1_end;
%let lag = -1;

/* Work with INTNX on valid SAS date values */
%let &P._start_l = %sysfunc(intnx(month, &&&p._start,&lag., same)) ;
%put &=p1_start_l;

%let SQL = WHERE date_var between %nrstr(%')%sysfunc(putn(&&&p._start_l,date9.))%nrstr(%') and %nrstr(%')%sysfunc(putn(&&&p._end,date9.))%nrstr(%') ;
%put &=sql;

So this approach, we no longer worry about putting dates into single quotes or double quotes and work with valid numeric SAS date values, and then at the end, we turn it back to something human readable as required by the problem.

I think also that there may be simpler approaches that don't involve &&& but you haven't shown us the whole macro, so we don't really know.

[Tom]

You can use %BQUOTE() to resolve macro logic inside of single quotes.

So let's do an example that does not overwrite its input.

Say you have these two macro variables that will work as dates in your SQL system (what SQL database uses dates in the style?)

%let SQL_start = '01JAN2007';
%let SQL_end   = '30JUN2007';

You can make a new string in that style like this:

%bquote('%sysfunc(intnx(month,&sql_start.d,-1),date9.)')

If you want add your indirect references to the macro variables (why? what value is there in overcomplicating things like that?) then use something like:

%let P = SQL;
%let lag = -1;

%let new_start = %bquote('%sysfunc(intnx(month,&&&p._start.d,&lag),date9.)');

But perhaps it would be clearer to do it in steps?

%let olddate=&&&p._start.d;
%let newdate=%sysfunc(intnx(month,&olddate,&lag),date9.);
%let newdate=%bquote('&newdate');

If the macro quoting causes trouble (and it shouldn't in pass thru SQL code) then add a call to %unquote().

%let newdate=%unquote(&newdate);

Or perhaps use %SYSFUNC() to call QUOTE() to add the single quotes.

%let newdate=%sysfunc(quote(%sysfunc(intnx(month,&olddate,&lag),date9.),%str(%')));