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

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

saurabhc — Fri, 15 Nov 2013 20:39:38 GMT

My data needs to look like this

id want yes/no

1 1 y

1 0 n

1 1 n

2 1 y

2 0 n

2 1 n

2 0 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.

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

Astounding — Fri, 15 Nov 2013 21:09:10 GMT

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;

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

ballardw — Fri, 15 Nov 2013 22:32:51 GMT

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.

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

Patrick — Sat, 16 Nov 2013 00:07:59 GMT

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;

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

saurabhc — Sat, 16 Nov 2013 00:13:07 GMT

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.

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

saurabhc — Sat, 16 Nov 2013 00:17:26 GMT

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.

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

saurabhc — Sat, 16 Nov 2013 00:25:00 GMT

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.