Hi Friends,
I need the logic help, below is the table, below is input statement and data which is grouped by sno.
input sno A B flag;
cards;
1 0 1 .
1 1 1 1
1 10 2 10
1 1 1 1
1 10 2 10
2 0 1 .
2 10 1 10
2 1 1 1
2 10 1 10
2 1 1 1
3 10 1 10
3 99 1 .
3 1 1 1
3 10 2 10
4 1 1 1
4 0 1 .
4 10 2 10
;
The logic that has to be accomplished :, within each by group (grouped by sno), if the first record has flag=1 then it should be output and rest of the records within the group should be ignored.
if the first record(grouped by sno) has flag=10, then it should proceed to the subsequent record and check for the flag till it finds either flag= 1 or 10 for first time with in the By group, if the found flag is 1 then rest of the records within the By group should be ignored, else if the found flag is 10 then all records including this record and rest of the records should be ignored with in the By group.
if the first record(group by sno) has neither flag= 1 or 10 then, it should continue searching for either flag=1 or 10 for the first time within the By group, if the found flag is 1 then rest of the records within the By group should be ignored, else if the found flag is 10 then all records including this record and rest of the records should be ignored with in the By group
The output for the above data should look like,
sno A B flag
1 0 1 .
1 1 1 1
2 0 1 .
3 10 1 10
3 99 1 .
3 1 1 1
4 1 1 1
Your help is much appreciated.
Thanks,
Saravanan
I found the logic for this very difficult to follow and execute until I understood that the effect of flag = 10 differed whether it was the first record in the group (when in effect it behaves like "other") or later in the sequence. So I defined xflag = flag except when it was the first record and flag=10, in which case xflag = 0. I defined snogroup to be the value of sno for the group (needed unless you do the logic in a separate data step with BY group processing), discard to be the effect on subsequent records in the group, and firstflag to be the first instance of xflag = 1 or 10 in the group.
While this answer delivers the required results for the data specified it wold not vastly surprise me if there was some combination of input records where the result was not as expected.
Richard in Oz
Data want ;
input sno A B flag;
Retain snogroup 0
discard 0
firstflag 0
;
xflag = flag ;
If NOT(snogroup = sno)
Then
Do ;
snogroup = sno ;
discard = 0 ;
firstflag = 0 ;
If flag = 10
then xflag = 0 ;
End ;
Select (xflag) ;
When (1)
Do ;
If Firstflag = 0
Then
Do ;
Firstflag = 1 ;
Output ;
Discard = 1 ;
End ;
If Firstflag = 10
Then
Do ;
Discard = 1 ;
End ;
End ;
When (10)
Do ;
If Firstflag = 0
Then
Do ;
Firstflag = 10 ;
End ;
If Firstflag = 10
Then
Do ;
Discard = 1 ;
End ;
End ;
Otherwise
Do ;
If Discard = 0
Then Output ;
End ;
End ;
Drop snogroup Discard Firstflag xflag ;
cards;
1 0 1 .
1 1 1 1
1 10 2 10
1 1 1 1
1 10 2 10
2 0 1 .
2 10 1 10
2 1 1 1
2 10 1 10
2 1 1 1
3 10 1 10
3 99 1 .
3 1 1 1
3 10 2 10
4 1 1 1
4 0 1 .
4 10 2 10
;
sno | A | B | flag |
---|---|---|---|
1 | 0 | 1 | . |
1 | 1 | 1 | 1 |
2 | 0 | 1 | . |
3 | 10 | 1 | 10 |
3 | 99 | 1 | . |
3 | 1 | 1 | 1 |
4 | 1 | 1 | 1 |
Hi Richard,
Thanks for the response. The logic works for above data and since you warned for some combination of records it might not work, i have changed the input data to accomodate whether 10 comes in the first records or in the later records. I have added an extra flag named flag1 which will let you know if 10 comes as a first record. is it now possible to come up with a robust logic with this modified data?. Thanks for your help
the logic that has to be accompished is:
within each by group (grouped by sno), if the first record has flag=1 & flag1=. then it should be output and rest of the records within the group should be ignored.
if the first record(grouped by sno) has (flag=10 & flag1=1) or (flag not in (1,10) & flag1=.), then it should proceed to the subsequent record and check for the flag till it finds either flag= 1 or 10 for first time with in the By group, if the found flag is 1 then rest of the records within the By group should be ignored, else if the found flag is 10 then all records including this record and rest of the records should be ignored with in the By group.
sno A B flag flag1
1 0 1 . .
1 1 1 1 .
1 10 2 10 .
1 1 1 1 .
1 10 2 10 .
2 0 1 . .
2 10 1 10 .
2 1 1 1 .
2 10 1 10 .
2 1 1 1 .
3 10 1 10 1
3 99 1 . .
3 1 1 1 .
3 10 2 10 .
4 1 1 1 .
4 0 1 . .
4 10 2 10 .
now the output will look like,
sno A B flag
1 0 1 . .
1 1 1 1 .
2 0 1 . .
3 10 1 10 1
3 99 1 . .
3 1 1 1 .
4 1 1 1 .
Thanks for all the help. it will be great to have the robust logic achieved.
Saravanan
Hi ,
I came up with the below code which seems to work robustly. Thanks for your help. Much appreciated.
data want(drop=_:);
set have;
by sno;
retain _twoflag _threeflag;
if first.sno & flag=1 & flag1=. then
do;
output;
_twoflag=0;
end;
else if (first.sno & flag=10 & flag1=1) or (first.sno & flag not in (1,10) & flag1=.) then
do;
output;
_twoflag=1;
_threeflag=0;
end;
if _twoflag=1 & first.sno ne 1 then
do;
if _threeflag=0 & flag not in (1,10) then output;
else if _threeflag=0 & flag=1 then
do;
output;
_threeflag=1;
end;
else if flag=10 then _threeflag=1;
end;
run;
Thanks all who tried to help me. much appreciated.
Saravanan
Hi,
I hope I have not oversimplified your problem. From my understanding, you only have the following two scenarios that flagging the obs not be output:
If so, we can have the following code:
data have;
input sno A B flag;
cards;
1 0 1 .
1 1 1 1
1 10 2 10
1 1 1 1
1 10 2 10
2 0 1 .
2 10 1 10
2 1 1 1
2 10 1 10
2 1 1 1
3 10 1 10
3 99 1 .
3 1 1 1
3 10 2 10
4 1 1 1
4 0 1 .
4 10 2 10
;
data want;
do _n_=1 by 1 until(last.sno);
set have nobs=nobs;
by sno;
if flag=1 then nobs=_n_;
if _n_>nobs then _f=1;
if _n_>1 and flag=10 then _f=1;
if _f ne 1 then output;
end;
drop _f;
run;
proc print;run;
Haikuo
Hi,
Thanks for the response. your code works perfectly.
Thanks a lot. Much appreciated.
Saravanan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: