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 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