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
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;
Thanks for your reply. It works perfect.
Much appreciated.
Saravanan
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;
Thanks for your reply. It works fine.
Much appreciated for your time and effort.
Saravanan
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.
I am using dif(). Don't you think I am a good learner?
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.
: 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;
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:
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.
Thanks for your effort and time. It works fine.
Saravanan.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: