Proc SQL: CASE WHEN nested

Question

Using nested case in Proc sql, I need to get the value of a column based on the specified year and month. For eg - If the year is 2018 and the month is december then it should return the value of the column x201811 and if it is null it should return zero. The code which I have written below is giving a syntax error. Please suggest a solution.

CASE  WHEN (( Year(date) = 2018 ) AND ( Month(date) = 12 )) THEN x201811 
     WHEN x201811 IS NULL THEN 0
   ELSE x201811
   WHEN ((Year(date)=2018) AND (Month(Date)=11)) THEN x201810
    WHEN x201810 IS NULL THEN 0
   ELSE x201810

Richard · Accepted Answer · 2018-03-19 11:28:57Z

1

You can use the COALESCE function to cause the first non-null value to become the assigned value.

CASE
  WHEN (( Year(date) = 2018 ) AND ( Month(date) = 12 )) 
  THEN coalesce (x201811, 0)

  WHEN ((Year(date)=2018) AND (Month(Date)=11)) 
  THEN coalesce (x201810, 0)

  ELSE coalesce (x201810, 0)
END as got_a_value

The alternative would be to nest CASE statements

CASE
  WHEN (( Year(date) = 2018 ) AND ( Month(date) = 12 )) 
  THEN CASE WHEN x201811 is null then 0 else x201811 end

  WHEN ((Year(date)=2018) AND (Month(Date)=11))
  THEN CASE WHEN x201810 is null then 0 else x201810 end

  ELSE ...
END as got_a_value

edited Mar 19, 2018 at 11:28

answered Mar 19, 2018 at 8:59

Richard

27.6k4 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Proc SQL: CASE WHEN nested

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related