topic Re: delete observation based on previous value in SAS Programming

delete observation based on previous value

Lulus — Sun, 21 Feb 2016 16:12:28 GMT

Hello All,

Now I have a question regarding how to to conditional deletion based on previous values.

my table looks like this. this is recording if someone breaks rule for each month.

have

ID seq happen

1 1 0

1 2 0

1 3 1

2 1 0

2 2 1

2 3 0

2 4 1

want

ID seq happen

1 1 0

1 2 0

1 3 1

2 1 0

2 2 1

as long as there is the first 1 appears, I would like to delete the following rows against each ID.

In term, what are to be left is a table with 0(never happen) and first 1 (happen the first time).

now my way of doing it is

1. add cumulative number after happen the first time

2, delete observation with value more than 1 from the original table

3, find the first 1 from the original table

4. merge the tables from 2 and 3

but I felt this is too redundent yet can find a more efficient way....

Please share with me how would you do this with a smarter processing idea.

I thougt of to set table until 1 against each ID and then ignore the rest boservations for this current ID and then

jump to the next ID with its first observation read,

but I don't know to code this in SAS....

Please share with me any thoughts...

thank you!

mohamed_zaki — Sun, 21 Feb 2016 16:39:11 GMT

data want;
set have;
retain delflag;
by ID;
if first.id then delflag=0;
if delflag=0 then output;
if happen=1 then delflag=1;
run;

Lulus — Sun, 21 Feb 2016 23:20:35 GMT

thank you very much mohamed_zaki, this is exactly what I wanted.

however I have a silly question.

if delflag=0 then output;
if happen=1 then delflag=1;

in the above two statement,

the lines with delflag=0 have been written to the want dataset and

how does the next if clause still truncate the rest of observations against the same id?

and even leave the first "happen=1" in the dataset?

I am trying to learn this logic. your response is very appreicated.