BookmarkSubscribeRSS Feed
Saravanan
Fluorite | Level 6

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

5 REPLIES 5
RichardinOz
Quartz | Level 8

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

;

snoABflag
101.
1111
201.
310110
3991.
3111
4111
Saravanan
Fluorite | Level 6

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

Saravanan
Fluorite | Level 6

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

Haikuo
Onyx | Level 15

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:

  1. 1. Any obs that is AFTER flag=1 within the same group;

  1. 2. Any obs that is INCLUDED AND AFTER (flag=10 and not the first obs in the group).

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

Saravanan
Fluorite | Level 6

Hi,

Thanks for the response. your code works perfectly.

Thanks a lot. Much appreciated.

Saravanan

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1294 views
  • 2 likes
  • 3 in conversation