Delete previous record based on conditions

[bob021]

Hi can somebody can help me with this?
Here is my data:

NAME NO SL CNT
Bob 35 a20 1
Bob 44 a22 2
Bob 28 a20 3
John 35 a20 1
John 35 a22 2
John 35 a20 3
John 35 a20 4
John 35 a22 5

Result I want :
NAME NO SL CNT
Bob 44 a22 2
Bob 28 a20 3
John 35 a22 2
John 35 a20 3
John 35 a22 5


I want to delete previous record based on conditions
My statement should look something like: if SL=a22 then delete previous (one above) record for same NAME. I'm not sure how to do this.
Thank you in advance.

[data_null__]

This method requires that you create a copy of the data with ID and CONDITION variables NAME and SL. It works but someone will have a better method.

[pre]
data test;
input NAME $ NO SL $ CNT;
cards;
Bob 35 a20 1
Bob 44 a22 2
Bob 28 a20 3
John 35 a20 1
John 35 a22 2
John 35 a20 3
John 35 a20 4
John 35 a22 5
;;;;
run;

data test2;
set test(keep=name sl);
run;
data test;
set test2;
by name;
if not first.name and sl eq 'a22' then do;
point = _n_-1;
modify test point=point;
remove;
end;
run;
proc print data=test;
run;[/pre]

[chang_y_chung_hotmail_com]

There are many ways to do this. Here is one using a hash obj. It assumes that cnt is without gaps or duplicates within a name.

   /* test data */

   data one;

      input name $ no sl $ cnt;

   cards;

   Bob 35 a20 1

   Bob 44 a22 2

   Bob 28 a20 3

   John 35 a20 1

   John 35 a22 2

   John 35 a20 3

   John 35 a20 4

   John 35 a22 5

   ;

   run;

   /* removes obs whose cnt is one less than the next one with sl="a22" */

   data two;

      dcl hash h();

      h.defineKey('cnt');

      h.defineDone();

      do until (last.name);

         set one;

         by name cnt;

         if sl="a22" then do;

            cnt = cnt - 1;

            h.add();   

         end;

      end;

      do until (last.name);

         set one;

         by name cnt;

         if h.check(key:cnt)=0 then continue;

         output;

      end;

      h.clear();

   run;

   proc print data=two;

   run; 

   /* on lst

   Obs    name    no    sl     cnt

    1     Bob     44    a22     2

    2     Bob     28    a20     3

    3     John    35    a22     2

    4     John    35    a20     3

    5     John    35    a22     5

   */

[deleted_user]

Hello,

Another one is based on reading same data twice:

[pre]
data in;
input NAME $ NO SL $ CNT;
datalines;
Bob 35 a20 1
Bob 44 a22 2
Bob 28 a20 3
John 35 a20 1
John 35 a22 2
John 35 a20 3
John 35 a20 4
John 35 a22 5
;

data out;
set in (firstobs=2) end=last;
set in (rename=(name=_name no=_no sl=_sl cnt=_cnt));

_n=_name;

if SL ne 'a22' or _n ne name then output;

if last then do _name=name;
_no=no;
_sl=sl;
_cnt=cnt;
output;
end;

drop name no sl cnt _n;

run;
[/pre]

Marius

[bob021]

Hi dat_null, chang, marius
Thank you very much for quick responds. All solutions’ works and help me a lot.
Bob021

[Ksharp]

Hi.
If you lik hash table which 's the best benefit is high efficient.
[pre]
data test;
input NAME $ NO SL $ CNT;
cards;
Bob 35 a20 1
Bob 44 a22 2
Bob 28 a20 3
John 35 a20 1
John 35 a22 2
John 35 a20 3
John 35 a20 4
John 35 a22 5
;;;;
run;
data result(keep=name no sl cnt);
declare hash find();
find.definekey('name','no','sl','cnt');
find.definedone();

do until(last);
count+1;
set test end=last;
if sl='a22' and name=lag(name) then do;
_point=count-1;
set test point=_point;
find.add();
end;
end;
do until(_last);
set test end=_last;
rc=find.check();
if rc ne 0 then output;
end;
stop;
run;
[/pre]


Or Maybe you want more simple code,then use Peter.C 's code


[pre]
data test;
input NAME $ NO SL $ CNT;
cards;
Bob 35 a20 1
Bob 44 a22 2
Bob 28 a20 3
John 35 a20 1
John 35 a22 2
John 35 a20 3
John 35 a20 4
John 35 a22 5
;;;;
run;
data _result(drop=_name _sl);
merge test test(firstobs=2 keep=name sl rename=(name=_name sl=_sl));
if name=_name and _sl='a22' then delete;
run;
[/pre]


Ksharp