Filter data in Proc SQL using date macro function

Accepted Solution Solved
Reply
Learner
Posts: 1
Accepted Solution

Filter data in Proc SQL using date macro function

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 = &macro_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 oneSmiley Happy 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.

 

 


Accepted Solutions
Solution
‎06-15-2018 03:12 PM
Super User
Posts: 13,577

Re: Filter data in Proc SQL using date macro function

[ Edited ]
Posted in reply to kandibrzoz

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.

View solution in original post


All Replies
Valued Guide
Posts: 597

Re: Filter data in Proc SQL using date macro function

[ Edited ]
Posted in reply to kandibrzoz

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 = "&macro_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. 

Thanks,
Suryakiran
Solution
‎06-15-2018 03:12 PM
Super User
Posts: 13,577

Re: Filter data in Proc SQL using date macro function

[ Edited ]
Posted in reply to kandibrzoz

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.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 131 views
  • 0 likes
  • 3 in conversation