Case when in SQL

[teddyee]

%MACRO C(CURR_DATE=,PCURR_DATE=, B=);
PROC SQL;
CREATE TABLE ADHOC.FINAL1_&B._&CURR_DATE. AS
SELECT A.Customer_IC_No

, CASE WHEN A.RBK_Last_Contactdate ='' and B.RBK_Last_Contactdate ='' THEN Max(A.RBK_Last_Contactdate,B.RBK_Last_Contactdate)
END AS RBK_Last_Contactdate FORMAT DATE9.

END AS new_filter

FROM ADHOC.final_&B._&PCURR_DATE. A
LEFT JOIN ADHOC.final_&B._&CURR_DATE. B
ON A.Customer_IC_No=B.Customer_IC_No
;
RUN;

%MEND;
%C(CURR_DATE=20170602,PCURR_DATE=20170601, B=M);

Question: In this example how do i use case when to get the latest date of the same variable from 2 files?

Thanks

[teddyee]

found it...

to replace with this line

, CASE WHEN COALESCE(A.RBK_Last_Contactdate, B.RBK_Last_Contactdate) is not null THEN max(A.RBK_Last_Contactdate, B.RBK_Last_Contactdate)
END AS RBK_Last_Contactdate FORMAT DATE9.

[Patrick · Accepted solution]

@teddyee

Your condition WHEN A.RBK_Last_Contactdate ='' and B.RBK_Last_Contactdate ='' only becomes TRUE when both variables are missing and though the max() will always be missing.

Your CASE statement is also missing an ELSE case so when one of the two variables isn't missing then nothing happens and you also end up with a missing.

And last but not least: 

Your code indicates that you're dealing with numerical variables so your comparison should be

A.RBK_Last_Contactdate=. and not A.RBK_Last_Contactdate=' '

I believe what you're after is something like:

CASE 
 WHEN cmiss(A.RBK_Last_Contactdate,B.RBK_Last_Contactdate)>0 
 THEN coalesce(A.RBK_Last_Contactdate,B.RBK_Last_Contactdate)
 else <some other value>
 END AS RBK_Last_Contactdate FORMAT DATE9.

Function CMISS() works for both character and numeric variables.

And for development and debugging purposes: Make your code work without macro syntax and only implement the macro bits once your code works.

[teddyee]

thanks it works.

another question: let's say if i want to get the max of the following characher Y, N, or missing variable. 

any solution?

thanks!

[teddyee]

let's say if i want to return a value
by ranking:
C>B>A>null

any proc sql code to get the max of the character?

i can get max of a numeric value, how to apply the same for character?