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
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;
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
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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.