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.
... View more