/*
Hi SAS Forum,
I have the below SAS data set which has 6 accts.
It shows all six accts start in January with status = 7 which means customer is deceased.
Then it shows how each deceased acct ages.
*/
data have;
input acct_num Jan_status Feb_status Mar_status Apr_status May_status Jun_status;
cards;
111 7 . 11 525 7 11
222 7 7 7 7 11 .
333 7 800 11 11 11 1
444 7 7 11 . . .
555 7 . . . . 1
666 7 . . . . .
;
run;
/*Question:
Among the 6 accts, acct 111 and 555 are having data quality problems. Why? becasue the deceased acct
disappear at least in a single middle month and then re-appear. These two accts are therefore problem accts.
My question is how to do the coding to flag these 2 accts as problem accts.
- Acct number 222 have no problem because it disappears sometime of its age but never re-aapears.
- Acct number 333 have no problem because it never disappears during its age.
- Acct number 444 have no problem because it disappears sometime of its age but never re-aapears.
-Acct number 666 have no problem because it disappears sometime of its age but never re-aapears.
*/
/*My attempt is below but it doesn't give correct resutls. Could some expert help me.*/
data want;
set have;
if Feb_status = . and Mar_status ^= . then flag = 'problem';
else if Mar_status = . and Apr_status ^= . then flag = 'problem';
else if Apr_status = . and May_status ^= . then flag = 'problem';
else if May_status = . and jun_status ^= . then flag = 'problem';
else flag = 'no prob';
;
run;
Thanks Mirisa
Hi,
Something like:
data have; input acct_num Jan_status Feb_status Mar_status Apr_status May_status Jun_status; cards; 111 7 . 11 525 7 11 222 7 7 7 7 11 . 333 7 800 11 11 11 1 444 7 7 11 . . . 555 7 . . . . 1 666 7 . . . . . ; run; data want (drop=miss); set have; array vals{6} jan_status feb_status mar_status apr_status may_status jun_status; miss=0; do i=1 to 6; if vals{i}=. then miss=1; else if miss=1 and vals{i} ne . then flag="Y"; end; run;
You could shrink the code somewhat, but that shows the process. I would also ask why you are making it hard for yourself? Not only are you working with transposed data (would be far better having a month column and the data goes down the page - would make your coding far simpler), but you have prefixed each variable with a different text string so you can't even use generic names. If you have labelled them status_<mon> then you could use status: to mean all of them.
Hi,
Something like:
data have; input acct_num Jan_status Feb_status Mar_status Apr_status May_status Jun_status; cards; 111 7 . 11 525 7 11 222 7 7 7 7 11 . 333 7 800 11 11 11 1 444 7 7 11 . . . 555 7 . . . . 1 666 7 . . . . . ; run; data want (drop=miss); set have; array vals{6} jan_status feb_status mar_status apr_status may_status jun_status; miss=0; do i=1 to 6; if vals{i}=. then miss=1; else if miss=1 and vals{i} ne . then flag="Y"; end; run;
You could shrink the code somewhat, but that shows the process. I would also ask why you are making it hard for yourself? Not only are you working with transposed data (would be far better having a month column and the data goes down the page - would make your coding far simpler), but you have prefixed each variable with a different text string so you can't even use generic names. If you have labelled them status_<mon> then you could use status: to mean all of them.
Hi RW9,
Your code is great. It worked like anything.
Thank you so much.
Mirisa
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.