topic Re: Case when in SQL in SAS Programming

Case when in SQL

teddyee — Sat, 24 Jun 2017 04:49:15 GMT

%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

Re: Case when in SQL

teddyee — Sat, 24 Jun 2017 05:20:35 GMT

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.

Re: Case when in SQL

Patrick — Sat, 24 Jun 2017 05:35:06 GMT

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

Re: Case when in SQL

teddyee — Sat, 24 Jun 2017 06:01:08 GMT

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!

Re: Case when in SQL

teddyee — Sat, 24 Jun 2017 07:16:34 GMT

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?