SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DO LOOP for summarizing a variable per multiple ID entries

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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       iud
3 3 iud
4 1 nonhormonal
4 2 nonhormonal
4 3 nonhormonal
4 4 nonhormonal
; run;

 


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

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;

View solution in original post


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

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 Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 289 views
  • 1 like
  • 2 in conversation