topic Re: Delete previous record based on conditions in SAS Procedures

Delete previous record based on conditions

bob021 — Mon, 07 Feb 2011 13:18:41 GMT

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.

Re: Delete previous record based on conditions

data_null__ — Mon, 07 Feb 2011 14:40:55 GMT

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]

Re: Delete previous record based on conditions

chang_y_chung_hotmail_com — Mon, 07 Feb 2011 15:16:25 GMT

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;

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

Re: Delete previous record based on conditions

deleted_user — Mon, 07 Feb 2011 15:57:03 GMT

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

Re: Delete previous record based on conditions

bob021 — Tue, 08 Feb 2011 07:51:03 GMT

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

Re: Delete previous record based on conditions

Ksharp — Thu, 17 Feb 2011 08:09:11 GMT

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