DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 170
Accepted Solution

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

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


Accepted Solutions
Solution
‎09-02-2011 01:42 PM
Occasional Contributor
Posts: 16

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

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.

View solution in original post


All Replies
Solution
‎09-02-2011 01:42 PM
Occasional Contributor
Posts: 16

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

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.

Regular Contributor
Regular Contributor
Posts: 170

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

Thanks Hobbes! Worked beautifully!

Regular Contributor
Posts: 184

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 787 views
  • 0 likes
  • 3 in conversation