Help using Base SAS procedures

Delete previous record based on conditions

Reply
Contributor
Posts: 60

Delete previous record based on conditions

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.
Respected Advisor
Posts: 3,799

Re: Delete previous record based on conditions

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]
Regular Contributor
Posts: 241

Re: Delete previous record based on conditions

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


   */


      

N/A
Posts: 0

Re: Delete previous record based on conditions

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

Contributor
Posts: 60

Re: Delete previous record based on conditions

Posted in reply to deleted_user
Hi dat_null, chang, marius
Thank you very much for quick responds. All solutions’ works and help me a lot.
Bob021
Super User
Posts: 10,035

Re: Delete previous record based on conditions

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
Ask a Question
Discussion stats
  • 5 replies
  • 1347 views
  • 0 likes
  • 5 in conversation