Calcite | Level 5

## 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?

3 REPLIES 3
Super User

## Re: Dropping observations after a certain value

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

Barite | Level 11

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

## 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;
``````