01-20-2016 11:39 AM
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)
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'
01-20-2016 11:48 AM - edited 01-20-2016 11:55 AM
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;
01-20-2016 12:27 PM
Do you want the first 'b'? If not this will do:
infile cards dsd;
input A B$;
if first.a then flag = 'N';
if b = 'b' then flag = 'Y';
if flag = 'N' then output;
01-20-2016 01:26 PM
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.