Case when in SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Case when in SQL

%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


Accepted Solutions
Solution
‎06-24-2017 02:01 AM
Respected Advisor
Posts: 4,173

Re: Case when in SQL

[ Edited ]

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

View solution in original post


All Replies
Occasional Contributor
Posts: 8

Re: Case when in SQL

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.

Solution
‎06-24-2017 02:01 AM
Respected Advisor
Posts: 4,173

Re: Case when in SQL

[ Edited ]

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

Occasional Contributor
Posts: 8

Re: Case when in SQL

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!

Occasional Contributor
Posts: 8

Re: Case when in SQL

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?
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 160 views
  • 0 likes
  • 2 in conversation