DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

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.



Accepted Solutions
Solution
‎11-15-2013 04:09 PM
Super User
Posts: 5,513

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

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;

View solution in original post


All Replies
Solution
‎11-15-2013 04:09 PM
Super User
Posts: 5,513

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

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;

Super User
Posts: 11,343

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

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.

Occasional Contributor
Posts: 19

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

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.

Respected Advisor
Posts: 4,173

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

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

data want(drop=_Smiley Happy;

  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;

Occasional Contributor
Posts: 19

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

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.

Occasional Contributor
Posts: 19

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

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 281 views
  • 3 likes
  • 4 in conversation