BookmarkSubscribeRSS Feed
bob021
Calcite | Level 5
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.
5 REPLIES 5
data_null__
Jade | Level 19
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
Obsidian | Level 7
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
Not applicable
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
Calcite | Level 5
Hi dat_null, chang, marius
Thank you very much for quick responds. All solutions’ works and help me a lot.
Bob021
Ksharp
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4845 views
  • 1 like
  • 5 in conversation