topic Re: How to separate subjects with multiple occurrences that satisfy a certain condition? in SAS Procedures

How to separate subjects with multiple occurrences that satisfy a certain condition?

Mirisage — Sun, 16 Sep 2012 19:57:16 GMT

Hi Colleagues,

In this data set, I need to separate the records where Arrears_Band at least once has “writoff” value.

data have;

informat Current_date date9.;

Input Current_date Account_number 11-13 Arrears_Band $ 15-25;

Format Current_date date9.;

cards;

31MAY2011 111 NPNA

30JUN2011 111 writoff

31JUL2011 111 NPNA

31AUG2011 111 NPNA

30SEP2011 111 NPNA

31OCT2011 111 NPNA

30NOV2011 111 NPNA

31DEC2011 111 NPNA

31JAN2012 111 NPNA

31DEC2011 222 NPNA

31JAN2012 222 NPNA

30NOV2010 333 NPNA

31DEC2010 333 NPNA

31JAN2011 333 NPNA

28FEB2011 333 NPNA

31MAR2011 333 writoff

30APR2011 333 NPNA

31MAY2011 333 NPNA

30JUN2011 333 NPNA

31JUL2011 333 NPNA

31AUG2011 333 NPNA

30SEP2011 333 NPNA

31OCT2011 333 NPNA

30NOV2011 333 NPNA

31DEC2011 333 NPNA

31JAN2012 333 NPNA

28FEB2010 444 Current

31MAR2010 444 30 - 60

30APR2010 444 30 - 60

31MAY2010 444 Current

30JUN2010 444 Current

31JUL2010 444 Current

31AUG2010 444 Current

30SEP2010 444 Current

31OCT2010 444 Current

30NOV2010 444 Current

31DEC2010 444 Current

31JAN2011 444 1 - 30

28FEB2011 444 30 - 60

31MAR2011 444 60 - 90

30APR2011 444 90 +

31MAY2011 444 90 +

30JUN2011 444 90 +

31JUL2011 444 NPNA

31AUG2011 444 NPNA

30SEP2011 444 NPNA

31OCT2011 444 NPNA

30NOV2011 444 NPNA

31DEC2011 444 NPNA

31JAN2012 444 NPNA

28FEB2010 555 30 - 60

31MAR2010 555 30 - 60

30APR2010 555 60 - 90

31MAY2010 555 NPNA

31JAN2012 666 writoff

31JAN2012 777 NPNA

;

run;

/*Answer*/

The data set I want is this.

Current_date Account_number Arrears_Band

31MAY2011 111 NPNA

30JUN2011 111 writoff

31JUL2011 111 NPNA

31AUG2011 111 NPNA

30SEP2011 111 NPNA

31OCT2011 111 NPNA

30NOV2011 111 NPNA

31DEC2011 111 NPNA

31JAN2012 111 NPNA

30NOV2010 333 NPNA

31DEC2010 333 NPNA

31JAN2011 333 NPNA

28FEB2011 333 NPNA

31MAR2011 333 writoff

30APR2011 333 NPNA

31MAY2011 333 NPNA

30JUN2011 333 NPNA

31JUL2011 333 NPNA

31AUG2011 333 NPNA

30SEP2011 333 NPNA

31OCT2011 333 NPNA

30NOV2011 333 NPNA

31DEC2011 333 NPNA

31JAN2012 333 NPNA

31JAN2012 666 writoff

Question:

I did below but it obviously selects only the specific records where Arrears_Band =writoff which is not I want.

Data want;

Set have;

If Arrears_Band =’writoff’;

Run;

Could anybody help me?

Thanks

Neil

Re: How to separate subjects with multiple occurrences that satisfy a certain condition?

Linlin — Sun, 16 Sep 2012 20:22:29 GMT

proc sql;

create table want as

select * from have

where account_number in (select account_number from have where Arrears_Band='writoff');

quit;

Re: How to separate subjects with multiple occurrences that satisfy a certain condition?

Haikuo — Sun, 16 Sep 2012 22:26:56 GMT

This is one of the classic topics on forum. In general, you would need two passes, no matter what kind of approach:

data have;

informat Current_date date9.;

Input Current_date Account_number Arrears_Band :$15.;

Format Current_date date9.;

cards;

31MAY2011 111 NPNA

30JUN2011 111 writoff

31JUL2011 111 NPNA

31AUG2011 111 NPNA

30SEP2011 111 NPNA

31OCT2011 111 NPNA

30NOV2011 111 NPNA

31DEC2011 111 NPNA

31JAN2012 111 NPNA

31DEC2011 222 NPNA

31JAN2012 222 NPNA

30NOV2010 333 NPNA

31DEC2010 333 NPNA

31JAN2011 333 NPNA

28FEB2011 333 NPNA

31MAR2011 333 writoff

30APR2011 333 NPNA

31MAY2011 333 NPNA

30JUN2011 333 NPNA

31JUL2011 333 NPNA

31AUG2011 333 NPNA

30SEP2011 333 NPNA

31OCT2011 333 NPNA

30NOV2011 333 NPNA

31DEC2011 333 NPNA

31JAN2012 333 NPNA

28FEB2010 444 Current

31MAR2010 444 30 - 60

30APR2010 444 30 - 60

31MAY2010 444 Current

30JUN2010 444 Current

31JUL2010 444 Current

31AUG2010 444 Current

30SEP2010 444 Current

31OCT2010 444 Current

30NOV2010 444 Current

31DEC2010 444 Current

31JAN2011 444 1 - 30

28FEB2011 444 30 - 60

31MAR2011 444 60 - 90

30APR2011 444 90 +

31MAY2011 444 90 +

30JUN2011 444 90 +

31JUL2011 444 NPNA

31AUG2011 444 NPNA

30SEP2011 444 NPNA

31OCT2011 444 NPNA

30NOV2011 444 NPNA

31DEC2011 444 NPNA

31JAN2012 444 NPNA

28FEB2010 555 30 - 60

31MAR2010 555 30 - 60

30APR2010 555 60 - 90

31MAY2010 555 NPNA

31JAN2012 666 writoff

31JAN2012 777 NPNA

;

run;

/*SQL, different construct comparing to Linlin's suggetion*/

proc sql;

select * from have group by account_number having sum(Arrears_Band='writoff')>=1;quit;

/*Interlace stacking*/

data want;

set have (in=up) have;

by account_number;

if first.account_number then call missing(flag);

if up and Arrears_Band='writoff' then flag+1;

if not up and flag>=1 then output;

drop flag;

run;

/*Classic 2X DOW*/

data want;

do until (last.account_number);

set have;

by account_number;

if Arrears_Band='writoff' then flag+1;

end;

do until (last.account_number);

set have;

by account_number;

if flag >=1 then output;

end;

call missing(flag);

drop flag;

run;

/*Merge*/

data want;

merge have (where=(Arrears_Band='writoff') in=a) have (in=b);

by account_number;

if a and b;

run;

/*Hash()*/

data want;

if _n_=1 then do;

if 0 then set have;

dcl hash h(dataset:'have', multidata:'y');

h.definekey('account_number');

h.definedata(all:'y');

h.definedone();

end;

do until (last.account_number);

set have;

by account_number;

if Arrears_Band='writoff' then flag+1;

end;

if flag>=1 then do;

rc=h.find();

do rc=0 by 0 while (rc=0);

output;

rc=h.find_next();

end;

call missing(flag);

drop flag rc;

run;

Haikuo

Re: How to separate subjects with multiple occurrences that satisfy a certain condition?

AUTigers — Sun, 16 Sep 2012 22:28:11 GMT

proc sql;

create table want as

select * from have

group by Account_number

having sum(lowcase(Arrears_Band)='writoff')>0

order by Account_number,current_date;

quit;

proc print data=want;run;