BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Baskarkannan
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Murray_Court
Quartz | Level 8

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;

View solution in original post

2 REPLIES 2
Murray_Court
Quartz | Level 8

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
Calcite | Level 5

Hi Murray,

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

Thanks,

Kannan B.

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!

How to Concatenate Values

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.

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
  • 2 replies
  • 883 views
  • 0 likes
  • 2 in conversation