## DO LOOP for summarizing a variable per multiple ID entries

Solved
Occasional Contributor
Posts: 17

# DO LOOP for summarizing a variable per multiple ID entries

[ Edited ]

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       iud3      3       iud4      1       nonhormonal     4      2       nonhormonal 4      3       nonhormonal4      4       nonhormonal
;
run;``````

Accepted Solutions
Solution
‎02-15-2016 03:44 PM
Super User
Posts: 23,323

## Re: DO LOOP for summarizing a variable per multiple ID entries

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;``````

All Replies
Solution
‎02-15-2016 03:44 PM
Super User
Posts: 23,323

## Re: DO LOOP for summarizing a variable per multiple ID entries

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;``````
Occasional Contributor
Posts: 17

## Re: DO LOOP for summarizing a variable per multiple ID entries

thanks, works perfectly. Fine with the note

🔒 This topic is solved and locked.