proc sql, case statement w/ today() date substitution

[_LB]

Hello all,

I have, what I think is a simple of piece of code that I am asking for a simple date subsititution during a case statement.

In essence, if the main date (DISCHDT) field is blank it goes to another table (DISCHARGE_DATE_ACTUAL) and retrieves from there. If that is blank, then I want today's date-

Code is:

CASE WHEN A.DischDt NE . THEN A.DischDt

     WHEN A.DischDt=. THEN DATEPART(DIS.DISCHARGE_DATE_ACTUAL)

     ELSE TODAY() end as discharge format MMDDYY10.

The problem is that it will not substitute today's date when both fields are blank. I have tried different variations to no effect.

Any help grateful.

Thanks & have a great labor day.

~LB

[Hobbes · Accepted solution]

Have you checked the coalesce function. In your case, something like:

coalesce(A.DischDt, DATEPART(DIS.DISCHARGE_DATE_ACTUAL), TODAY()) as discharge

will do the job.

[_LB]

Thanks Hobbes! Worked beautifully!

[Howles]

Logic is incorrect. The two WHEN branches exhaust all possibilities, so the ELSE branch can never be triggered. The second WHEN branch of the CASE should test the alternate date; something like this ...

WHEN  DIS.DISCHARGE_DATE_ACTUAL NE . THEN DATEPART(DIS.DISCHARGE_DATE_ACTUAL)

~LB wrote:
Hello all,
I have, what I think is a simple of piece of code that I am asking for a simple date subsititution during a case statement.
In essence, if the main date (DISCHDT) field is blank it goes to another table (DISCHARGE_DATE_ACTUAL) and retrieves from there. If that is blank, then I want today's date-
Code is:
CASE WHEN A.DischDt NE . THEN A.DischDt 
     WHEN A.DischDt=. THEN DATEPART(DIS.DISCHARGE_DATE_ACTUAL)
     ELSE TODAY() end as discharge format MMDDYY10.
The problem is that it will not substitute today's date when both fields are blank. I have tried different variations to no effect.
Any help grateful.
Thanks & have a great labor day.
~LB