BookmarkSubscribeRSS Feed
pankak
Calcite | Level 5

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




4 REPLIES 4
pankak
Calcite | Level 5

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


Kurt_Bremser
Super User

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?

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 811 views
  • 0 likes
  • 3 in conversation