Exclude records with perticular id based on condition

Reply
Contributor
Posts: 35

Exclude records with perticular id based on condition

I'm learning sas......Trying to do below example....Please suggest me how to do it.
Pls post the code.

Below is the sample data.

input:

id date status days
112 03/02/09 Approved 4
112 03/03/09 Refferal 3
112 03/04/09 shipped 2
112 03/06/09 shipped 3
113 04/01/08 Refferal 2
113 04/02/09 Approved 1
113 04/03/09 Ahipped 1

I want to exclude the recordswith perticular id in output,if first date status is not equal to refferal.

Output:

id date status days
113 04/01/08 Refferal 2
113 04/02/09 Approved 1
113 04/03/09 Ahipped 1

Thanks,
sasg
Valued Guide
Posts: 2,111

Re: Exclude records with perticular id based on condition

SASg,

First, a note of forum etiquette: just post a question to one forum.

Here is a solution. It uses the RETAIN statement to create a variable that can be used as a flag to determine if the first record is a "Refferal".

This could also be done with PROC SQL if one assumed the first date in an ID group is also the earliest and is unique within the group. Both of those conditions exist in your example, but may not be there in a larger data set.

Doc Muhlbaier
Duke

DATA first;
FORMAT DATE MMDDYY8.;
INFORMAT date mmddyy8.;
LENGTH status $8.;
input id date status days;
cards;
112 03/02/09 Approved 4
112 03/03/09 Refferal 3
112 03/04/09 shipped 2
112 03/06/09 shipped 3
113 04/01/08 Refferal 2
113 04/02/09 Approved 1
113 04/03/09 Ahipped 1
run;

DATA results;
SET first;
BY id;
RETAIN flag;
IF first.id THEN
IF status='Refferal' THEN flag=1;
ELSE flag=0;
IF flag=0 THEN DELETE;
DROP flag;
RUN;
Ask a Question
Discussion stats
  • 1 reply
  • 319 views
  • 0 likes
  • 2 in conversation