topic Re: Hi All,Please help me in solving the below scenario in SAS Procedures

Hi All,Please help me in solving the below scenario

pankak — Thu, 07 May 2015 05:31:20 GMT

I have a dataset in the following format with millions of observation.

data test;

infile datalines;

input id_acc acc_no status info_date;

datalines;

111 9586 aaa 30jun2007

111 9586 aaa 31jul2007

111 9586 d1a 31aug2007

111 9999 lep 30sep2007

111 9586 lep 30sep2007

111 9999 lep 31Oct2007

111 9586 lep 31Oct2007

111 9999 lep 30Nov2007

111 9586 lep 30Nov2007

112 9587 d1a 30jun2007

112 9587 aaa 31jul2007

112 9587 d2a 31aug2007

112 9587 d1d 30sep2007

112 9991 lep 31Oct2007

112 9587 lep 31Oct2007

112 9991 lep 30Nov2007

112 9587 lep 30Nov2007

112 9991 lep 31Dec2007

112 9587 lep 31Dec2007

I want the output as mentioned below.

Only i want to keep the first two info_date when the card goes into lep and rest only one dates of lep.

output:

111 9586 aaa 30jun2007

111 9586 aaa 31jul2007

111 9586 d1a 31aug2007

111 9999 lep 30sep2007

111 9586 lep 30sep2007

111 9999 lep 31Oct2007

111 9999 lep 30Nov2007

112 9587 d1a 30jun2007

112 9587 aaa 31jul2007

112 9587 d2a 31aug2007

112 9587 d1d 30sep2007

112 9991 lep 31Oct2007

112 9587 lep 31Oct2007

112 9991 lep 30Nov2007

112 9991 lep 31Dec2007

Re: Hi All,Please help me in solving the below scenario

Kurt_Bremser — Thu, 07 May 2015 07:21:56 GMT

Your output does not match your description. You keep much more dates than the two "lep" dates.

Re: Hi All,Please help me in solving the below scenario

pankak — Thu, 07 May 2015 09:02:24 GMT

Hi It is the same output:

111 9586 aaa 30jun2007

111 9586 aaa 31jul2007

111 9586 d1a 31aug2007

111 9999 lep 30sep2007

111 9586 lep 30sep2007

111 9999 lep 31Oct2007

111 9586 lep 31Oct2007-delete this row

111 9999 lep 30Nov2007

111 9586 lep 30Nov2007-delete this row

112 9587 d1a 30jun2007

112 9587 aaa 31jul2007

112 9587 d2a 31aug2007

112 9587 d1d 30sep2007

112 9991 lep 31Oct2007

112 9587 lep 31Oct2007

112 9991 lep 30Nov2007

112 9587 lep 30Nov2007-delete this row

112 9991 lep 31Dec2007

112 9587 lep 31Dec2007-delete this row...

So i want to delete these particular row and keep first occurance of dates where it goes into lep two time and rest only one time.

output:

111 9586 aaa 30jun2007

111 9586 aaa 31jul2007

111 9586 d1a 31aug2007

111 9999 lep 30sep2007

111 9586 lep 30sep2007

111 9999 lep 31Oct2007

111 9999 lep 30Nov2007

112 9587 d1a 30jun2007

112 9587 aaa 31jul2007

112 9587 d2a 31aug2007

112 9587 d1d 30sep2007

112 9991 lep 31Oct2007

112 9587 lep 31Oct2007

112 9991 lep 30Nov2007

112 9991 lep 31Dec2007

Re: Hi All,Please help me in solving the below scenario

Kurt_Bremser — Thu, 07 May 2015 10:58:25 GMT

So, for every id_acc, you want to eliminate multiple dates after the first two "lep" states have been encountered?

I also guess that once an id_acc has entered "lep" state, it does not revert back?

Re: Hi All,Please help me in solving the below scenario

Ksharp — Thu, 07 May 2015 12:54:08 GMT

Assuming I understand what you are asking for .

data have;
input a b c $ d : $20.;
cards;
111   9586     aaa   30jun2007
111   9586     aaa   31jul2007
111   9586     d1a   31aug2007
111   9999     lep   30sep2007
111   9586     lep   30sep2007
111   9999     lep   31Oct2007
111   9586     lep   31Oct2007
111   9999     lep   30Nov2007
111   9586     lep   30Nov2007
112   9587     d1a   30jun2007
112   9587     aaa   31jul2007
112   9587     d2a   31aug2007
112   9587     d1d   30sep2007
112   9991     lep   31Oct2007
112   9587     lep   31Oct2007
112   9991     lep   30Nov2007
112   9587     lep   30Nov2007
112   9991     lep   31Dec2007
112   9587     lep   31Dec2007
;
run;
data want;
 set have;
 by a d notsorted;
 if first.a then lep=0;
 if first.d and c='lep' then lep+1;

 if first.d then n=0;
 n+1;

 if lep ne 1 and n ne 1 then delete;
run;

Xia Keshan