How to select only the first continuous set of satisfying criteria and ignore the rest in a "long" table?

[saurabhc]

My data needs to look like this

id     want     yes/no

1     1          y

1     1          y

1     1          y

1     0          n

1     0          n

1     1          n

1     1          n

2     1          y

2     1          y

2     0          n

2     1          n

2     1          n  

2     0          n

2     1          n

2     1          n

basically for each id, I want "y" only for the first continuous want = 1. From the point want = 0, I want to ignore the subsequent rows for that di even if want =1 again for that id.

This is represented by "y" and "n". I need to know how to code so I get the right "y"s and "n"s. I tried do until and do while but my inexperience makes them inexecutable.

Thanks.

[Astounding · Accepted solution]

There must be a more elegant way, but this should do it:

data want;

  set have;

  by id want notsorted;

  retain yes_no  any_1s ' ';

  if first.id then do;

     any_1s='n';

     yes_no='n';

  end;

  if first.want then do;

     if want=1 and any_1s='n' then do;

        yes_no='y';

        any_1s='y';

    end;

     else yes_no='n';

  end;

  drop any_1s;

run;

[ballardw]

There may be some ambiguity in your requirement to "ignore". Do mean to delete the data or not execute some additional code?

You also say " so I get the right "y"s and "n"s." but you don't address which the "right" n's would be.

[saurabhc]

Hi ballardw,

What I meant is this, keep flagging or counting or whatever, until the want changes to a zero. Once a want changes to zero, it does not matter even if it changes to a want = 1 again for that particular id. Basically, I just want the first "set" of continuous want = 1 for any given id and ignore the want = 0 and the subsequent want = 1 (in sets or single).

Ignore could mean drop the observation or just change the flag or stop counting or whatever (like above). The idea is the same.

Sorry about the ambiguity.

Thanks!

PS: All ids always start with want = 1 in my dataset.

[Patrick]

Does below code give you the result you're after?

data want(drop=_:);

  set have;

  by id want notsorted;

  if first.id then _counter=0;

  if first.want and want=1 then _counter+1;

  if want=1 and _counter=1 then yn_flg='y';

  else yn_flg='n';

run;

[saurabhc]

Hi Patrick,

Thank you. Your code works too! It is nice to know how different lines produce the same result because they make different use of similar logic.

Thanks again.

[saurabhc]

Hi Astounding,

The code you gave totally works after adjusting it to my actual data. Basically there were also multiple dates with the ids but they did not matter as long as my indicator "want" had preselected my desirable observations.