Help on data removal ..

[Baskarkannan]

Need help for below scenario

I have data set as like below. I want to remove the members those who are in lapsed status from the data set  (i.e Where status = 1 in the latest date) .

option compress = yes;

data sample;

input member_id status change_date DDMMYY10.;

format change_date date9.;

datalines;

123 2 10072013

123 3 18072013

123 1 03082013

456 1 08082013

456 2 17082013

789 3 14052013

;

run;

proc print data = sample;

run;

In the above data set member_id 123 having 3 observations. The third observations 123 1 03082013 is the latest record also having status = 1 . So The member should not come to the report. It needs to be eliminated.

The member_id 456 having 2 observations. The second observations 456 2 17082013 is the latest record also having status = 2 . So The member should come to the report.

Please help me on this.

Thanks,

Kannan B.

[Murray_Court · Accepted solution]

Hi,

To make this clear is it only the last instance of of a member where the status is examined, with all instances being deleted if lapsed and all being kept if not?

Also, is it the last record with respect to time or with respect to position on the dataset? I notice that for 123 the change date on the last record occurs chronologically before the date on the previous two records.

Whatever the case then this program should help:

/* sort by member, and if necessary by date */

proc sort data=sample out=sample2;

by member_id, change_date;

run;

/* make list of member ids that fall within lapsed category*/

data exclsn_list;

set sample2;

by member_id; /*adds first and last automatic variables*/

if last.member_id AND status=1;

run;

/*exclude based on our list */

proc sql;

create table clean_sample as

select * from sample

where member_id not in

(select member_id from exclsn_list)

;

quit;

/* print result*/

proc print data=clean_sample;

run;

[Baskarkannan]

Hi Murray,

  This concepts are working properly. Thanks much for your help on this..

Thanks,

Kannan B.