topic Re: Deleting rows based on condition in SAS Programming

Deleting rows based on condition

Saravanan — Tue, 06 Nov 2012 14:15:09 GMT

Hi Friends,

I have a simple problem, I wrote the logic but the logic fails to work properly when i run it in the actual data.

All i have to do is delete the rows after flag within the group. I have pasted my data(grouped by sno) , desired output and my code below. The code works fine with this data but fails on the real data which might have other combinations.

In the below data, all i have to do is delete all the rows which follows the first occurence of flag=1 within the group(grouped by sno).

I appreciate your time and help.

data test_del;
input sno A B flag;
cards;
1 1 2 0
1 1 1 0
1 1 1 1
1 1 1 0
1 1 1 0
2 2 2 0
2 2 2 1
2 2 2 0
3 3 3 1
3 3 3 0
3 3 3 1
3 3 3 0
4 4 4 0
4 4 4 0
4 4 4 1
;
run;

my output should look like:

sno A B flag

1 1 2 0

1 1 1 0

1 1 1 1

2 2 2 0

2 2 2 1

3 3 3 1

4 4 4 0

4 4 4 1

My code which fails to run on real data but runs on this test data.
data test_del1;
set test_del;
by sno;
retain fl;
if first.sno & flag ne 1 then fl=0;
if first.sno & flag =1 then output;
if fl ne 1 then output;
if flag =1 then fl =1;
run;

Sincerely,

Saravanan

Re: Deleting rows based on condition

art297 — Tue, 06 Nov 2012 14:22:48 GMT

How about?:

data test_del1 (drop=fl);

set test_del;

by sno;

retain fl;

if fl ne 1 then do;

output;

if flag eq 1 then fl=1;

end;

if last.sno then fl=0;

run;

Re: Deleting rows based on condition

Linlin — Tue, 06 Nov 2012 14:31:24 GMT

data test_del;

input sno A B flag;

cards;

1 1 2 0

1 1 1 0

1 1 1 1

1 1 1 0

2 2 2 0

2 2 2 1

2 2 2 0

3 3 3 1

3 3 3 0

3 3 3 1

3 3 3 0

4 4 4 0

4 4 4 1

;

run;

data want;

set test_del;

by sno ;

retain c;

if first.sno then c=1;

if dif(flag)=-1 then c+1;

if c<2;

run;

proc print;run;

Re: Deleting rows based on condition

Astounding — Tue, 06 Nov 2012 14:33:59 GMT

Here's my attempt:

data test_del1;

set test_del;

by sno;

if first.sno then delete_me='N';

retain delete_me;

if delete_me='N' then output;

if flag=1 then delete_me='Y';

drop delete_me;

run;

Good luck.

Re: Deleting rows based on condition

Linlin — Tue, 06 Nov 2012 14:42:38 GMT

I am using dif(). Don't you think I am a good learner?

Re: Deleting rows based on condition

Saravanan — Tue, 06 Nov 2012 14:49:47 GMT

Thanks for your reply. It works perfect.

Much appreciated.

Saravanan

Re: Deleting rows based on condition

Saravanan — Tue, 06 Nov 2012 14:51:05 GMT

Thanks for your reply. It works fine.

Much appreciated for your time and effort.

Saravanan

Re: Deleting rows based on condition

Saravanan — Tue, 06 Nov 2012 14:51:50 GMT

Thanks for your effort and time. It works fine.

Saravanan.

Re: Deleting rows based on condition

Astounding — Tue, 06 Nov 2012 14:56:43 GMT

Ahh, yes I see you are paying good attention Linlin. Remember, genius is 1% inspiration, 98% perspiration, and 2% attention to detail.

Upon closer inspection, I think Art's suggestion and mine are extremely similar. I would guess that mine is easier to read, but Art's is slightly faster.

Re: Deleting rows based on condition

art297 — Tue, 06 Nov 2012 15:05:07 GMT

: I'm glad to see you learning new functions but, in this case, your logic will not always work correctly. e.g., it will not exclude any records in the following scenario when, I think, it should only be keeping the first record for each sno:

data test_del;

input sno A B flag;

cards;

1 1 2 1

1 1 1 1

2 2 2 1

3 3 3 1

4 4 4 1

;

run;

data want;

set test_del;

by sno ;

retain c;

if first.sno then c=1;

if dif(flag) then c+1;

if c<2;

run;

Re: Deleting rows based on condition

Linlin — Tue, 06 Nov 2012 15:25:36 GMT

Hi Art,

You are right. In that case my code would not work. Would you please read your post in the link (Soap box): https://communities.sas.com/thread/32053

I was scared to death by your comments when I just joined the forum a year ago. The good thing is that I am not afraid of you anymore!:smileylaugh::smileylaugh::smileylaugh:

Re: Deleting rows based on condition

art297 — Tue, 06 Nov 2012 15:33:52 GMT

Linlin, I've never intended to scare anyone with my comments and glad to hear that you're not afraid of me.

About my soapbox comment, however long ago that was, it doesn't apply here as the requirement was to keep the first record (within a sno) that had a flag=1, but delete all remaining records.