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

Struggling with date variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 89
Accepted Solution

Struggling with date variables

In DI Studio, I have an extract transformation that needs to extract all rows where &extract_dttm is between valid_from_dttm and valid_to_dttm. So, I've set up this basic where-statement to start with:

&extract_dttm BETWEEN valid_from_dttm AND valid_to_dttm;

valid_from_dttm and valid_to_dttm are numeric variables with the format NLDATM21.

&extract_dttm is set up by means of this precode:

%let extract_dttm = %sysfunc(putn(%sysfunc(intnx(DTMONTH, %sysfunc(DATETIME()), -1, E)), NLDATM21.));

When using %put &extract_dttm; it appears like this in the log:

30.sep.12 23:59:59    (which looks just like valid_from_dttm and valid_to_dttm in the input table)

Apparently &extract_dttm can't be compared to the numeric valid_from_dttm and valid_to_dttm because it's a character. How can I edit the precode or where-statement to successfully extract the correct rows?

I'd greatly appreciate any insight, as I've been struggling with variations of this code for days now. I'm very inexperienced in using date and macro variables.

Thanks for your time. Smiley Happy


Accepted Solutions
Solution
‎10-30-2012 04:29 PM
Super User
Posts: 17,819

Re: Struggling with date variables

All macro variables are characters.

To use them as date or date time values you can enclose them in quotations with the appropriate letters afterwards.

IE.

Date:

"01Apr2011"d

Date Time

"25dec2000:15:01"dt

View solution in original post


All Replies
Solution
‎10-30-2012 04:29 PM
Super User
Posts: 17,819

Re: Struggling with date variables

All macro variables are characters.

To use them as date or date time values you can enclose them in quotations with the appropriate letters afterwards.

IE.

Date:

"01Apr2011"d

Date Time

"25dec2000:15:01"dt

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 200 views
  • 0 likes
  • 2 in conversation