DATA Step, Macro, Functions and more

delete observation based on previous value

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

delete observation based on previous value

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!


Accepted Solutions
Solution
‎02-21-2016 04:00 PM
Super Contributor
Posts: 490

Re: delete observation based on previous value

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;

View solution in original post


All Replies
Solution
‎02-21-2016 04:00 PM
Super Contributor
Posts: 490

Re: delete observation based on previous value

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;
Contributor
Posts: 46

Re: delete observation based on previous value

Posted in reply to mohamed_zaki

thank you very much 

 

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 299 views
  • 1 like
  • 2 in conversation