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!
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.