topic Data rule for horizontal records? in SAS Programming

Data rule for horizontal records?

dunga — Mon, 22 Aug 2016 14:30:12 GMT

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

Re: Data rule for horizontal records?

RW9 — Mon, 22 Aug 2016 14:40:44 GMT

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.

Re: Data rule for horizontal records?

dunga — Mon, 22 Aug 2016 17:11:20 GMT

Hi RW9,

Your code is great. It worked like anything.

Thank you so much.

Mirisa