CODEWITHSUNDEEP
sas
Autumn
Autumn

Reputation: 585

How to delete record according to previous row condition?

I am wondering if I can modify or delete records based on the value in the previous row. For example,

data test;
    input id seq var $ @@;
    datalines;
    1 1 Y
    1 2 .
    2 1 N
    2 2 a
;
run;

For each id, I would like to delete where seq = 2 if (var = 'Y' where seq = 1). For example, for id=1, if seq = 1 and var = 'Y' then delete where id=1 and seq=2.

Upvotes: 1

Views: 1335

Answers (3)

DaBigNikoladze
DaBigNikoladze

Reputation: 671

Easiest in Data step :)

data output;
    set test;
    if (seq=2 and lag(seq)=1 and lag(var)='Y') then delete;
run;

Upvotes: 1

Joe
Joe

Reputation: 63434

Easiest in SQL:

proc sql;
  delete from test T where exists (
    select 1 from test E where E.id=T.id and E.seq=1 and E.var='Y')
    and seq=2;
quit;

Data step is also possible:

data want;
  set test;
  retain deleteme;
  if seq=1 then do;
    if var='Y' then deleteme=1;
    else deleteme=0;
  end;
  else if seq=2 and deleteme=1 then delete;
run;

You need to retain the value across records.

Upvotes: 1

Kay
Kay

Reputation: 365

you can use if condition in your data step to delete..

data want;
     set test;
     if(seq eq 2 and var ne "") then delete;
end;

The condition which you have mentioned is bit confusing where u mentioned var eq 1 and var eq 2.

So, according to your condition u can modify the above code condition.

Note: In SAS missing values for numeric is .(dot/period). however for character variable it is blank. if u intentionally make this is as dot.. it is fine. then in the above code it will be var ne '.'.

Upvotes: 0

Related Questions