BookmarkSubscribeRSS Feed
OrangePiper
Calcite | Level 5

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
ballardw
Super User

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;

 

 

Steelers_In_DC
Barite | Level 11

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;

FreelanceReinh
Jade | Level 19

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 595 views
  • 0 likes
  • 4 in conversation