SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

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

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. Smiley Happy


Accepted Solutions
Solution
‎10-25-2012 05:14 AM
Super Contributor
Posts: 644

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

Posted in reply to RichardinOz

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

View solution in original post


All Replies
Super Contributor
Posts: 644

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

Posted in reply to TurnTheBacon

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

Super Contributor
Posts: 644

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

Posted in reply to RichardinOz

If you want to check the date,

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

30SEP12:23:59:59

Solution
‎10-25-2012 05:14 AM
Super Contributor
Posts: 644

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

Posted in reply to RichardinOz

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

Frequent Contributor
Posts: 89

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

Posted in reply to RichardinOz

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. Smiley Happy

Frequent Contributor
Posts: 89

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

Posted in reply to 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. Smiley Happy

Super User
Super User
Posts: 7,083

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

Posted in reply to TurnTheBacon

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



🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 461 views
  • 3 likes
  • 3 in conversation