DO loop to indicate if something occurred at least once

Solved
Occasional Contributor
Posts: 17

DO loop to indicate if something occurred at least once

HI there, back again.

I need to create a summary dichotomous indicator variable for each ID to indicate if they used a specific method (iud) at least one time in any of their visits. It can be "attached" to the  last observation.

data have:

ID   visit   method

1      1       inj

1      2       inj

1      3       inj

1      4       .

1      5       iud

2      1      ocp

2      2      ocp

2      3      .

3      1      iud

3      2      iud

data want:

ID   visit   method  tried

1      1       inj

1      2       inj

1      3       inj

1      4       .

1      5       iud       1

2      1      ocp

2      2      ocp

2      3      ocp       0

3      1      iud

3      2      iud        1

Accepted Solutions
Solution
‎02-15-2016 03:18 AM
Super User
Posts: 10,691

Re: DO loop to indicate if something occurred at least once

``````data have;
input ID   visit   method  \$;
cards;
1      1       inj
1      2       inj
1      3       inj
1      4       .
1      5       iud
2      1      ocp
2      2      ocp
2      3      .
3      1      iud
3      2      iud
;
run;
data want;
do until(last.id);
set have;
by id;
if method='iud' then found=1;
end;
do until(last.id);
set have;
by id;
if  last.id then tried=ifn(found=1,1,0); output;
end;
drop found;
run;``````

All Replies
Super User
Posts: 5,852

Re: DO loop to indicate if something occurred at least once

Storing inforamtion like that on the last observation isn't really a good practice (depending on what you wist to di with is later on).

In anormalized structire you would sore this on a ID level table (untested!):

``````proc sql;
create table ID_master as
select id,
case when
sum((case method when 'iud' then 1 else 0 end)) = 0
then 0
else 1
end as tried
from have
group by id
;
quit
``````
Data never sleeps
Occasional Contributor
Posts: 17

Re: DO loop to indicate if something occurred at least once

HI LinusH, thank you for your response. I tried your code and it created a table but returned all 0 values to the HTID. Other ideas?

Also,I think you are right about storing the indicator variable onthe last id row, so what is a better alternative? Thanks!

Solution
‎02-15-2016 03:18 AM
Super User
Posts: 10,691

Re: DO loop to indicate if something occurred at least once

``````data have;
input ID   visit   method  \$;
cards;
1      1       inj
1      2       inj
1      3       inj
1      4       .
1      5       iud
2      1      ocp
2      2      ocp
2      3      .
3      1      iud
3      2      iud
;
run;
data want;
do until(last.id);
set have;
by id;
if method='iud' then found=1;
end;
do until(last.id);
set have;
by id;
if  last.id then tried=ifn(found=1,1,0); output;
end;
drop found;
run;``````
Occasional Contributor
Posts: 17

Re: DO loop to indicate if something occurred at least once

thanks, Ksharp, this worked!
🔒 This topic is solved and locked.