DATA Step, Macro, Functions and more

Data rule for horizontal records?

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Data rule for horizontal records?

/*

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


Accepted Solutions
Solution
‎08-22-2016 01:09 PM
Super User
Super User
Posts: 7,942

Re: Data rule for horizontal records?

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.

View solution in original post


All Replies
Solution
‎08-22-2016 01:09 PM
Super User
Super User
Posts: 7,942

Re: Data rule for horizontal records?

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.

Contributor
Posts: 38

Re: Data rule for horizontal records?

Hi RW9,

 

Your code is great. It worked like anything.

 

Thank you so much.

 

Mirisa

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 220 views
  • 0 likes
  • 2 in conversation