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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.