Dropping observations after a certain value

Reply
Frequent Learner
Posts: 1

Dropping observations after a certain value

I have a data set with lets say 2  variables A & B (in actual fact there are around 70 variables but these are the only two I am interested in)

 

A, B, 

1, a, 

1, b,

1, c,

1, c

2, d

2, d

2, d

2, b

2, b

3, a

3, c

3, b

3, b

3, d

 

 

I only want to output observations for each A value up until the B value equals B. So from first.A until B no longer = 'b', and this is repeated for each A value. I'm trying to use a do while loop but it doesn't seem to be working.

 

So in this example it would keep the first two observations where A = 1

the first 5 observations where A = 2

and the first 4 observations where A = 3.

 

The values of B before B = 'b' are not always the same and can be made up of any combination of values except 'b'

 

Any Ideas?

 

Super User
Posts: 10,550

Re: Dropping observations after a certain value

[ Edited ]

Will you ever have a case where the 'b' values aren't sequential such as this?

A, B, 

1, a, 
1, b,
1, c,
1, b

Is the case where the group starts with 'b' treated any differently?

 

A, B, 
1, b, 
1, b,
1, c,
1, c

would still return 2 values?

If no to both of those this may work for you

data want;
   set have;
   by notsorted a notsorted b;
   retain flag 0;
   if first.a then flag=1;
   if B='b' and last.b then do;
      output;
      flag=0;
   end;
   if flag then output;
   drop flag;
run;

 

 

Valued Guide
Posts: 858

Re: Dropping observations after a certain value

Do you want the first 'b'?  If not this will do:

 

data have;
infile cards dsd;
input A  B$;
cards;
1, a
1, b
1, c
1, c
2, d
2, d
2, d
2, b
2, b
3, a
3, c
3, b
3, b
3, d
;run;

data want;
set have;
by a;
retain flag;
if first.a then flag = 'N';
if b = 'b' then flag = 'Y';
if flag = 'N' then output;
drop flag;
run;

Trusted Advisor
Posts: 1,115

Re: Dropping observations after a certain value

With the same requirements as for @ballardw's solution you could use a DO-UNTIL loop:

data want;
do until(last.A);
  set have;
  by A B notsorted;
  if ~flag then output;
  if B='b' & last.B then flag=1;
end;
drop flag;
run;

For background information about this special usage of the DO-UNTIL loop ("DOW loop"), please see, for example, http://www2.sas.com/proceedings/sugi28/099-28.pdf.

Ask a Question
Discussion stats
  • 3 replies
  • 307 views
  • 0 likes
  • 4 in conversation