/*
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.