Hi everyone,
I need to write an array or do loop maybe to scan all values for an indicator variable and if there is a single "1" then output to a new variable a "1" for ALL observations (rows) for that specific ID.
I wrote this code below to try it, but it just outputs a "1" for the new variable triedhc in the rows where it is true, but not for ALL rows for the htid so I think I need to put it in a do loop or array but my attempts aren't working.
data want; set have; by htid;
triedhc=IFN(method in('T_IUD','T_INJ','T_OCP'),1,0);
run;
data have;
input ID visitnum method $;
cards;
1 1 inj
1 2 inj
1 3 inj
1 4 .
1 5 inj
2 1 nonhormonal
2 2 nonhormonal
2 3 .
3 1 iud
3 2 iud
3 3 iud
4 1 nonhormonal
4 2 nonhormonal
4 3 nonhormonal
4 4 nonhormonal
;
run;
Process is:
1. Find values for each ID
2. Apply to all values of that ID
This can be done via a merge, DOW loop, SQL.
The solution below is SQL and does leave a Note in the log so it's up to you to decide if it's efficient. If notes are an issue, I'd suggest doing it in a two step process.
proc sql;
create table want as
select a.*, max(case when method in ('inj' 'iud') then 1
else 0
END) as Found
from have as a
group by ID
order by id, visitnum;
quit;
Process is:
1. Find values for each ID
2. Apply to all values of that ID
This can be done via a merge, DOW loop, SQL.
The solution below is SQL and does leave a Note in the log so it's up to you to decide if it's efficient. If notes are an issue, I'd suggest doing it in a two step process.
proc sql;
create table want as
select a.*, max(case when method in ('inj' 'iud') then 1
else 0
END) as Found
from have as a
group by ID
order by id, visitnum;
quit;
thanks, works perfectly. Fine with the note 🙂
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.