%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
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.
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.
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.
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.