topic Re: Dropping observations after a certain value in SAS Programming

Dropping observations after a certain value

OrangePiper — Wed, 20 Jan 2016 16:39:30 GMT

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, b

3, a

3, c

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?

Re: Dropping observations after a certain value

ballardw — Wed, 20 Jan 2016 16:55:05 GMT

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;

Re: Dropping observations after a certain value

Steelers_In_DC — Wed, 20 Jan 2016 17:27:41 GMT

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

FreelanceReinh — Wed, 20 Jan 2016 18:26:04 GMT

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.