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?
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;
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;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.