DO LOOP for summarizing a variable per multiple ID entries

[mcdj]

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;

[Reeza · Accepted solution]

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;

[mcdj]

thanks, works perfectly. Fine with the note 🙂