topic Re: Macro variables for date and integer in SAS Studio

Macro variables for date and integer

Wickedestjr — Thu, 02 Nov 2023 21:20:15 GMT

Hello,

I am struggling to incorporate three different macro variables into a program that otherwise works just fine. I've tried searching for solutions online, but am struggling to find anything that fixes these problems.

The macro variables are set up at the beginning of the program:

%let month_number = 8;

%let month = 202308;

%let day = 20230831;

Firstly, I am trying to create a table:

data table2;

set table1;

if PROCESS_YYYYMM <= (INCURRED_DT_YR_2) * 100 + &month_number.

then...

...

I'm getting a syntax error when I try using the &month_number. variable.

Secondly, the program has a step where it connects to a server and creates a table with the following WHERE statement:

WHERE

t1.PROCESS_YYYYMM between '201601' and '202308' and

t2.INCURRED_DT_DAY between '20160101' and '20230831'

The program works just fine like this, but I'd like to incorporate the &month. and &day. variables in lieu of the end dates shown here. How do I need to format this? I've tried a number of different possibilities with apostrophes, quotation marks, and even tried using a %syslput line for each variable before this table gets created, but nothing that I've tried seems to work. I keep getting ERROR: CLI execute error and the table doesn't get created.

Any help greatly appreciated!

Re: Macro variables for date and integer

Tom — Thu, 02 Nov 2023 21:33:55 GMT

If the macro is generating SAS code then just use double quotes.

     t1.PROCESS_YYYYMM between '201601' and "&month"
 and t2.INCURRED_DT_DAY between '20160101' and "&day"

If you are pushing that code into some external database that does not recognize double quotes are character constants then you will have to work a little harder to get the single quotes instead. Try:

     t1.PROCESS_YYYYMM between '201601' and %bquote('&month')
 and t2.INCURRED_DT_DAY between '20160101' and %bquote('&day')

Re: Macro variables for date and integer

Wickedestjr — Thu, 02 Nov 2023 22:35:05 GMT

Thank you Tom! That worked.

Do you know what I should try for the month_number variable?

Re: Macro variables for date and integer

Tom — Thu, 02 Nov 2023 23:32:29 GMT

If the code comparing the variable to a quoted string worked then you don't want to treat it as if it was a number like you have in the other code. Unless you are talking about two different variables (from different datasets) that just happen to have the same name.

So what do you think this code is doing?

    if PROCESS_YYYYMM <= (INCURRED_DT_YR_2) * 100 + &month_number.

What is INCURRED_DT_YR_2? Is that a numeric variable? What types of values does it have?

It it is a NUMBER than convert it to a character string and append a character version of &MONTH_NUMBER.

    if PROCESS_YYYYMM <=  put(INCURRED_DT_YR_2,Z4.) || put( &month_number.,Z2.)