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.
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;
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;
Hi Murray,
This concepts are working properly. Thanks much for your help on this..
Thanks,
Kannan B.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.