%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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.