I need to automate reporting and am having difficulty filtering data in my proc sql statement using a date macro function based on SAS date. The sas properties for the date on the table I need to filter are as follows:
Type: Numeric
Group: Date
Length (in bytes): 8
Format: datetime20.
Informat: datetime20.
If I hard code the date, this works:
%let system_date = '01JAN2017:00:00:00'DT ;
However, I cannot automate the report with hard coded dates.
The date macro functions I have tried are:
%let macro_date= %sysfunc(putn(%eval(%sysfunc(today())-0), datetime20.));
%put macro_date = ¯o_date ;
but I get this result: macro_date = 01JAN1960:05:55:50
I have also tried pulling the data from an existing table where the date is already in a datetime20. format and using symput to declare it as a macro variable and it fails when I use it in the filter for my original table. See example below for day_date:
proc sql;
create table date_facts as select day_date
from d_time
where datepart(day_date) = today()-1 ;
quit ;
data _null_ ;
set date_facts ;
call symput('system_date',put(day_date,datetime20.)) ; (I've tried this but it looks like it is forcing two leading spaces)
call symput('system_date2',compress(put(day_date,datetime20.))||" ") ;(and this to remove the leading spaces and put them at the end)
run ;
%put system_date = &system_date ;
%put system_date2 = &system_date2 ;
**results of the two symputs:
system_date= 14JUN2018:00:00:00
system_date2=14JUN2018:00:00:00
Using them in the filter below:
proc sql ;
create table date_file as select distinct account, end_date
from sls.sales_table
where end_date > &system_date ;
(or alternately using this one:) where end_date > &system_date2 ;
quit ;
I get this error for both:
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "SYSTEM_DATE".
14JUN2018:00:00:00
_______
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
~=.
ERROR 76-322: Syntax error, statement will be ignored.
quit ;
NOTE: The SAS System stopped processing this step because of errors.
I've also tried this:
proc sql ;
create table date_file as select distinct account, end_date
from sls.sales_table
where end_date > '&system_date2'dt ;
quit ;
and get this error:
ERROR: Invalid date/time/datetime constant '&system_date2'dt.
I am at my wits end trying to get this to work. Any help you can offer is greatly appreciated.
The TODAY function yields a date, which is number of days from 1 Jan 1960. So when you format it with a DATETIME format, which expects the number of seconds you get a date part of the datetime value close to 1 Jan1960.
You can get a datetime using the DHMS function with the today() in the date position and in the remaining parameters
In a data step this would look like:
dt = dhms(today(),0,0,0);
or pick different time elements if needed.
Instead of spending time building date or datetime literal values, which appears you are doing just use the numeric value.
A comparison of
if thisvar > 21349 then ..
is just as valid as
if thisvar > '14JUN2018'd then ...
and will require less manipulation such as Putn.
Literal values may be important when humans read code but generated code for the computer often makes more sense to leave the numeric. Also the way you are currently using the macro variable as
where end_date > &system_date
would be incorrect if the system_date contains 14JUN2018:00:00:00.
if your end_date is a datetime value then the comparison would be
where end_date > "&system_date"dt
and if end_date is character getting the correct comparison would be more a matter of luck as any date of the 15th a month would be greater than the character value. Test it yourself to see if "15JAN2010:00:00:00" is greater than "14DEC2018:00:00:00".
If the purpose is to compare a date to a date that you can calculate to create a macro variable then use the similar calculation directly in the code:
if end_date > dhms(today(),0,0,0)
or if you need an offset such as the beginning of the month nest the date portion with a call to the intnx function.
In your first try, your giving today() which is only date and no time, so change to datetime()
%let macro_date= %sysfunc(putn(%sysevalf(%sysfunc(datetime())-0), datetime20.));
%put macro_date = "¯o_date"DT ;
Using this macro your WHERE clause has to be WHERE end_date>"macro_date"DT
In you second attempt instead of call symput use CALL SYMPUTX
proc sql ;
create table date_file as select distinct account, end_date
from sls.sales_table
where end_date > "&system_date"DT
;
QUIT;
You need to tell SAS that your give date value in where condition.
The TODAY function yields a date, which is number of days from 1 Jan 1960. So when you format it with a DATETIME format, which expects the number of seconds you get a date part of the datetime value close to 1 Jan1960.
You can get a datetime using the DHMS function with the today() in the date position and in the remaining parameters
In a data step this would look like:
dt = dhms(today(),0,0,0);
or pick different time elements if needed.
Instead of spending time building date or datetime literal values, which appears you are doing just use the numeric value.
A comparison of
if thisvar > 21349 then ..
is just as valid as
if thisvar > '14JUN2018'd then ...
and will require less manipulation such as Putn.
Literal values may be important when humans read code but generated code for the computer often makes more sense to leave the numeric. Also the way you are currently using the macro variable as
where end_date > &system_date
would be incorrect if the system_date contains 14JUN2018:00:00:00.
if your end_date is a datetime value then the comparison would be
where end_date > "&system_date"dt
and if end_date is character getting the correct comparison would be more a matter of luck as any date of the 15th a month would be greater than the character value. Test it yourself to see if "15JAN2010:00:00:00" is greater than "14DEC2018:00:00:00".
If the purpose is to compare a date to a date that you can calculate to create a macro variable then use the similar calculation directly in the code:
if end_date > dhms(today(),0,0,0)
or if you need an offset such as the beginning of the month nest the date portion with a call to the intnx function.
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.
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.