Compare converted date to another date in proc sql case statement

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Compare converted date to another date in proc sql case statement

I am trying to use a proc sql case statement where I compare a date that I converted from datetime to date using datepart.  When I do this same converstion and simply select it, it works fine and matches the date in the dataset I am comparing to.  The case statement, however, keeps giving me an error no matter what I try.  Here is the latest code.

 

proc sql;

create table want as

select

 case
      when (f.edm_acquisition_identifier = 'CMCMAY0113'
            and datepart(f.edm_acquisition_date) FORMAT=MMDDYY10. as Acquisitionconv = dn.Date
            and dn.CMC_Name = 'PN6')
      or (f.edm_acquisition_identifier = 'CMCMAY1613'
                and datepart(f.edm_acquisition_date) FORMAT=MMDDYY10. = dn.Date
                and dn.CMC_Name = 'PN6') then 'Date1'

     when ......

 

END as mydate

;

quit;

 

As I mentioned, if I simply run a select statement with this conversion the date looks perfect.

 

datepart(f.edm_acquisition_date) FORMAT=MMDDYY10.

 

Here is the section of the error log where it is failing:

 

, case
33                     when (f.edm_acquisition_identifier = 'CMCMAY0113'
34                         and datepart(f.edm_acquisition_date) FORMAT=MMDDYY10. as Acquisitionconv = dn.Date
                                                    ______
                                                    22
                                                    76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET,
              GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

35                         and dn.CMC_Name = 'PN6')
36                     or (f.edm_acquisition_identifier = 'CMCMAY1613'
37                         and datepart(f.edm_acquisition_date) FORMAT=MMDDYY10. = dn.Date
38                         and dn.CMC_Name = 'PN6') then 'Date1'


Accepted Solutions
Solution
‎03-17-2016 09:02 AM
Grand Advisor
Posts: 17,338

Re: Compare converted date to another date in proc sql case statement

Why are you trying to convert it to a character string?

If their both sas date variables, the comparison will work, regardless of format. 

View solution in original post


All Replies
Grand Advisor
Posts: 17,338

Re: Compare converted date to another date in proc sql case statement

Remove the format from the comparisons. Formats only control appearance. 

Frequent Contributor
Posts: 138

Re: Compare converted date to another date in proc sql case statement

Ok, I didn't realize that.  I was thinking it worked like the PUT functions for some reason.

 

However, when I use the PUT function to convert from a SAS date to MMDDYYYY it is converting it to a character string.

 

PUT(datepart(f.edm_acquisition_date),MMDDYYS10.)

 

So, I am now getting an error that the data I am comparing is of different data types.

Solution
‎03-17-2016 09:02 AM
Grand Advisor
Posts: 17,338

Re: Compare converted date to another date in proc sql case statement

Why are you trying to convert it to a character string?

If their both sas date variables, the comparison will work, regardless of format. 

Frequent Contributor
Posts: 138

Re: Compare converted date to another date in proc sql case statement

I was trying to convert the SAS date 123456 to a MMDDYYYY to match the other dataset.  The SAS date was showing as a numeric data type so I thought I had to get it into a date data type.  I didn't realize I could compare the numeric SAS date to the date data type and it work properly. 

 

This correted it.  Thank you for the help.

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 787 views
  • 1 like
  • 2 in conversation