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.
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
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.