Deleting rows based on condition

Reply
Contributor
Posts: 60

Deleting rows based on condition

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 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

PROC Star
Posts: 7,366

Re: Deleting rows based on condition

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;

Contributor
Posts: 60

Re: Deleting rows based on condition

Thanks for your reply. It works perfect.

Much appreciated.

Saravanan

Super Contributor
Posts: 1,636

Re: Deleting rows based on condition

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;

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;

Contributor
Posts: 60

Re: Deleting rows based on condition

Thanks for your reply. It works fine.

Much appreciated for your time and effort.

Saravanan

Super User
Posts: 5,099

Re: Deleting rows based on condition

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.

Super Contributor
Posts: 1,636

Re: Deleting rows based on condition

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

Super User
Posts: 5,099

Re: Deleting rows based on condition

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.

PROC Star
Posts: 7,366

Re: Deleting rows based on condition

: 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

1 1 1 1

1 1 1 1

1 1 1 1

2 2 2 1

2 2 2 1

2 2 2 1

3 3 3 1

3 3 3 1

3 3 3 1

3 3 3 1

4 4 4 1

4 4 4 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;

Super Contributor
Posts: 1,636

Re: Deleting rows based on condition

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:

PROC Star
Posts: 7,366

Re: Deleting rows based on condition

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.

Contributor
Posts: 60

Re: Deleting rows based on condition

Thanks for your effort and time. It works fine.

Saravanan.

Ask a Question
Discussion stats
  • 11 replies
  • 346 views
  • 4 likes
  • 4 in conversation