BookmarkSubscribeRSS Feed
WalterC
Calcite | Level 5
I am trying to set up a report with the input dates always today and 1 week back. I am able to get these macro variables into the correct form as strings, but when I go to try to use them in a sql query (below) the fact that they are strings causes an error. I have thought about just using a data step and changing the format in the datastep, but for this particular query a datastep takes forever. Any suggestions would be very helpful.

73 %let end_date_value =%sysfunc(today());
74 %put &end_date_value;
18003
75
76 %let start_date_value = %eval(&end_date_value-7);
77 %put &start_date_value;
17996
78
79 %let start_date = %sysfunc(putn(&start_date_value, date9.));
80 %put &start_date;
09APR2009
81
82 %let end_date = %sysfunc(putn(&end_date_value, date9.));
83 %put &end_date;
16APR2009


proc sql;
create table P as
select *

from table as rt
where date >= &start_date
and date <= &end_date

;
quit;
2 REPLIES 2
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Explore using SAS data literals where the string is surrounded in double-quotes and suffixed with a "D" character, telling SAS that the character literal is to be interpreted as a DATE. Or depending on the format (if not ddmmmyyyy or ddmmmyy format) you could also use the SAS INPUT function to read up a character string and let SAS covert it to a SAS numeric variable (internall represented as days since 1/1/1960).

Have a look at the SAS support http://support.sas.com/ website and use the SEARCH facility to look for the phrase Working with Dates - you will find several DOCUMENTATION links and supplemental technical/conference papers referenced.

Scott Barry
SBBWorks, Inc.
WalterC
Calcite | Level 5
Thanks! I didn't know about using the double quoted literals. Previously when I used the single quotes the macro wouldn't be evaluated. The double quotes seem to fix the problem if I use the %str function.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 678 views
  • 0 likes
  • 2 in conversation