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 🙂
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.
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.