Deleting rows based on condition

[Saravanan]

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

[art297]

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;

[Saravanan]

Thanks for your reply. It works perfect.

Much appreciated.

Saravanan

[Linlin]

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;

[Saravanan]

Thanks for your reply. It works fine.

Much appreciated for your time and effort.

Saravanan

[Astounding]

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.

[Linlin]

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

[Astounding]

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.

[art297]

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

[Linlin]

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:

[art297]

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.

[Saravanan]

Thanks for your effort and time. It works fine.

Saravanan.