Making a precode macro variable work properly for use in a where-statement

[TurnTheBacon]

I'm using the following precode in an Extract transformation:

%let extract_dttm = intnx('DTMONTH',DATETIME(),&back.,'E');

%put &extract_dttm;

The purpose of the Extract, if run today, is to extract all rows with extract_dttm between Valid_From_DTTM and Valid_From_DTTM

The &back parameter is -1, to designate the previous month.

When I run the Extract, %put &extract_dttm prints the text "intnx('DTMONTH',DATETIME(),-1,'E')" to the log. How do I turn this into a value? I need to be able to use it in this WHERE-statement:

&extract_dttm. BETWEEN Valid_From_DTTM AND Valid_From_DTTM

Thanks for your time.

[RichardinOz]

You have a mix of Base and Macro:  the macro compiler will treat intnx as text.

Use %sysfunc to bring intnx into the macro compiler.  May also need %sysevalf

Try this

     %let extract_dttm = %sysevalf(%sysfunc(intnx(DTMONTH,%sysfunc(DATETIME()),&back.,E))) ;

     %put &extract_dttm;

Regards

Richard in Oz

[RichardinOz]

If you want to check the date,

     %Put %Sysfunc(putn(&extract_dttm, datetime.)) ;

30SEP12:23:59:59

[RichardinOz · Accepted solution]

Here is the all-singing, all-dancing version

%macro Datelimit (back) ;

    %local set_dttm ;

    %Let set_dttm = %sysevalf(%sysfunc(intnx(DTMONTH,%sysfunc(DATETIME()),&back.,E))) ;

    %* no semiclolon on next line ;

    %Unquote(%Str(%')%sysfunc(Putn(&set_dttm, datetime20.))%str(%')DT)

%Mend ;

%Put Where extract_dttm Between %Datelimit(-3) and %Datelimit(-1) ;

Where extract_dttm Between '  31JUL2012:23:59:59'DT and '  30SEP2012:23:59:59'DT

Enjoy

Richard in Oz

[TurnTheBacon]

Thanks, turns out I didn't need the %sysevalf for it to run. The log now shows me the value 1667347199 for &extract_dttm. However, I'm still unclear on how I can make the Extract work as intended, to extract all values where &extract_dttm is between Valid_From_DTTM and Valid_To_DTTM.

The where-statement says: &extract_dttm. BETWEEN Valid_From_DTTM AND Valid_From_DTTM.

&extract_dttm currently has the value 1667347199, while Valid_From_DTTM and Valid_To-DTTM have the NLDATM21-format, appearing as for example 02SEP12:12:31:02.

What can I do in the where-statement to turn Valid_From_DTTM and Valid_From_DTTM into the same kind of format as &extract_dttm? I assume that's the way to go.

Sorry for the newbie'ish question.

[TurnTheBacon]

One of the rows in the input table has these values:

VALID_FROM_DTTM: 02OCT12:12:31:02

VALID_TO_DTTM: 01JAN99:00:00:00

Using RichardinOz's helpful suggestion, the log confirms that &extract_dttm has this value: 31OCT12:23:59:59.

Yet, the following where-statement in the Extract doesn't extract any values:

&extract_dttm BETWEEN VALID_FROM_DTTM AND VALID_TO_DTTM

This is the precode I'm using (which generates the correct value 31OCT12:23:59:59😞

%let extract_dttm = %Sysfunc(putn(%sysfunc(intnx(DTMONTH, %sysfunc(DATETIME()), &back., E)), datetime.));

How can I modify the where-statement or precode to resolve this, so that the Extract works as intended? I've tried using ""dt on the variables in numerous combinations, but perhaps I've missed one.

UPDATE: Sorry, resolved, thanks again Richard.

[Tom]

You need to use four digit years in your datetime literals.  Depending on the setting of the yearcutoff option '99' could be interpreted as '1999' or '2099'.

data test ;

input (fromdt todt) (:datetime20.);

format fromdt todt datetime20. ;

put (_all_) (=);

cards;

02OCT12:12:31:02 01JAN99:00:00:00

02OCT2012:12:31:02 01JAN2099:00:00:00

run;

fromdt=02OCT2012:12:31:02 todt=01JAN1999:00:00:00

fromdt=02OCT2012:12:31:02 todt=01JAN2099:00:00:00

Datetime variables in SAS are numbers that store the number of seconds since 01JAN1960. So the time 31OCT2012:23:59:59 will be stored as the number 1667347199.  You can specify a specific datetime value by that number or you can use a datetime literal to make it easier for humans to read.

If you have a macro variable with the value 31OCT2012:23:59:59 and you want to use it in a WHERE statement to compare it to values in a datetime variable then just use the datetime literal format.

WHERE "&extract_dttm"dt BETWEEN VALID_FROM_DTTM AND VALID_TO_DTTM

Now if your dataset variables are character strings instead of actual datetime values then you need to convert those to numbers to get the comparison to work. 

WHERE "&extract_dttm"dt BETWEEN input(VALID_FROM_DTTM,datetime.) AND input(VALID_TO_DTTM,datetime.)