How to group and keep the observations in between two values from two variables

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to group and keep the observations in between two values from two variables

hi,

I want to group the blocks into the same group and just want keep those groups. In other words, I just want the blocks in between "first" in flag1 and "last" in flag2.

Anybody has simple idea? 

thanks in advance.

Z Chen

---------------------------------------------------

data have;

input obs flag1 $ flag2 $;

datalines;

  1 na na            

  2 na na

  3 first na

  4 na na

  5 first  last

6 na na

  7 na na

  8 na na

  9 na na

10 na na

11 na  last

12 na na

13 na na

14 na na

15 na na

23 na na

24 na na

25 first na

26 na na

27 na na

32 na last

;

run;

proc print data=have;

run;


Accepted Solutions
Solution
‎11-01-2013 10:26 PM
Respected Advisor
Posts: 4,934

Re: How to group and keep the observations in between two values from two variables

You could do it this way:

data open(keep=open) close(keep=close);
set have;
open = _n_;
close = _n_;
if flag1="first" then output open;
if flag2="last" then output close;
run;

data want;
set open;
set close;
group + 1;
do i = open to close;
     set have point=i;
     output;
     end;
drop open close i;
run;

PG

PG

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: How to group and keep the observations in between two values from two variables

Given your example, what do you want the output file to look like?

Contributor
Posts: 40

Re: How to group and keep the observations in between two values from two variables

I want the output looks like this:

obs flag1  flag2  group;

  3 first na        1

  4 na na          1

  5 first  last     1

  5 first  last     2

6 na na          2

  7 na na          2

  8 na na          2

  9 na na          2

10 na na          2

11 na  last          2

25 first na          3

26 na na          3

27 na na          3

32 na last          3

Solution
‎11-01-2013 10:26 PM
Respected Advisor
Posts: 4,934

Re: How to group and keep the observations in between two values from two variables

You could do it this way:

data open(keep=open) close(keep=close);
set have;
open = _n_;
close = _n_;
if flag1="first" then output open;
if flag2="last" then output close;
run;

data want;
set open;
set close;
group + 1;
do i = open to close;
     set have point=i;
     output;
     end;
drop open close i;
run;

PG

PG
Contributor
Posts: 40

Re: How to group and keep the observations in between two values from two variables

Simple and elegant! you are genius. thanks a lot! Good night and good weekend.

ZC

Respected Advisor
Posts: 4,934

Re: How to group and keep the observations in between two values from two variables

You are welcome. If you want to do some error checking for unbalanced first and last flags you could replace the first step with

data open(keep=open) close(keep=close);

set have end=check;

open = _n_;

close = _n_;

if flag1="first" then do;

     output open;

     level + 1;

     end;

if flag2="last" then do;

     output close;

     level + (-1);

     end;

if level < 0 then do;

     put "ERROR: Unbalanced last found at " obs=;

     put (_all_) (=);

     stop;

     end;

if check and level > 0 then

     put "WARNING: " level "missing last flag(s).";

run;

PG

PG
PROC Star
Posts: 7,492

Re: How to group and keep the observations in between two values from two variables

or you could use:

data want (drop=start);

  set have;

  retain start;

  if flag1 eq 'first' then do;

    start=1;

    output;

    group+1;

    if flag2 eq 'last' then output;

  end;

  else do;

    if start then output;

    if flag2 eq 'last' then start=0;

  end;

run;

Contributor
Posts: 40

Re: How to group and keep the observations in between two values from two variables

another great solution. Thanks!

ZC

Respected Advisor
Posts: 3,156

Re: How to group and keep the observations in between two values from two variables

Very interesting question. Here is another one, somewhat similar to Art's solution:

data have;

input obs flag1 :$ flag2 :$8.;

datalines;

  1 na na           

  2 na na

  3 first na

  4 na na

  5 first  last

6 na na

  7 na na

  8 na na

  9 na na

10 na na

11 na  last

12 na na

13 na na

14 na na

15 na na

23 na na

24 na na

25 first na

26 na na

27 na na

32 na last

;

data want;

length flag2 $8;

retain _id;

  do _n_=1 by 1 until (flag2='last');

    set have;

  if flag1='first' then _id=_n_;

    if not missing(_id) and _id <= _n_ then output;

    if flag2='last' then do;

       if flag1='first' then _id=0; else call missing(_id);

    end;

  end;

run;

Haikuo

Contributor
Posts: 40

Re: How to group and keep the observations in between two values from two variables

helpful answer, but not exactly what i want. I don't want the obs 5 itself as a group. PGStats's answer is exactly what I want.

thanks, I believe a little bit modification of your codes will do the fix.

Z

Respected Advisor
Posts: 3,156

Re: How to group and keep the observations in between two values from two variables

Aha, I must have skipped your downstream post that shows your intended output. And you are right, a tweak on my current code will do:

data have;

input obs flag1 $ flag2 $;

datalines;

  1 na na           

  2 na na

  3 first na

  4 na na

  5 first  last

6 na na

  7 na na

  8 na na

  9 na na

10 na na

11 na  last

12 na na

13 na na

14 na na

15 na na

23 na na

24 na na

25 first na

26 na na

27 na na

32 na last

;

data want;

length flag2 $8;

retain _id;

  do _n=1 by 1 until (flag2='last');

    set have;

  if flag1='first' then do; _id=_n;end;

    if not missing(_id) and _id <= _n then do; group=_n_;output;end;

    if flag2='last' then do;

       if flag1='first' then do;group=_n_+1;output; _id=0;end; else call missing(_id);

    end;

  end;

  drop _:;

run;

Haikuo

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 302 views
  • 6 likes
  • 4 in conversation