topic Re: Making a precode macro variable work properly for use in a where-statement in SAS Data Management

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

TurnTheBacon — Thu, 25 Oct 2012 08:44:31 GMT

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.

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

RichardinOz — Thu, 25 Oct 2012 08:55:05 GMT

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

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

RichardinOz — Thu, 25 Oct 2012 09:02:04 GMT

If you want to check the date,

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

30SEP12:23:59:59

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

RichardinOz — Thu, 25 Oct 2012 09:14:49 GMT

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

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

TurnTheBacon — Thu, 25 Oct 2012 09:25:55 GMT

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.

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

TurnTheBacon — Thu, 25 Oct 2012 10:53:45 GMT

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.

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

Tom — Thu, 25 Oct 2012 11:56:37 GMT

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.)