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.
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;
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;
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.
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.
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;
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.