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

DO loop to indicate if something occurred at least once

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

DO loop to indicate if something occurred at least once

HI there, back again.

 

I need to create a summary dichotomous indicator variable for each ID to indicate if they used a specific method (iud) at least one time in any of their visits. It can be "attached" to the  last observation.

 

data have:

ID   visit   method 

1      1       inj        

1      2       inj      

1      3       inj

1      4       .

1      5       iud

2      1      ocp

2      2      ocp

2      3      .

3      1      iud

3      2      iud

 

data want:

ID   visit   method  tried

1      1       inj        

1      2       inj         

1      3       inj

1      4       .

1      5       iud       1

2      1      ocp      

2      2      ocp

2      3      ocp       0

3      1      iud       

3      2      iud        1

 


Accepted Solutions
Solution
‎02-15-2016 03:18 AM
Super User
Posts: 10,044

Re: DO loop to indicate if something occurred at least once

data have;
input ID   visit   method  $;
cards;
1      1       inj        
1      2       inj      
1      3       inj
1      4       .
1      5       iud
2      1      ocp
2      2      ocp
2      3      .
3      1      iud
3      2      iud
 ;
 run;
 data want;
do until(last.id);
  set have;
  by id;
  if method='iud' then found=1; 
end;
do until(last.id);
  set have;
  by id;
  if  last.id then tried=ifn(found=1,1,0); output;
end;
drop found;
run;

View solution in original post


All Replies
Super User
Posts: 5,437

Re: DO loop to indicate if something occurred at least once

Storing inforamtion like that on the last observation isn't really a good practice (depending on what you wist to di with is later on).

In anormalized structire you would sore this on a ID level table (untested!):

 

proc sql;
   create table ID_master as
      select id, 
             case when
                 sum((case method when 'iud' then 1 else 0 end)) = 0
                 then 0
                 else 1
             end as tried
         from have
         group by id
    ;
quit
Data never sleeps
Occasional Contributor
Posts: 17

Re: DO loop to indicate if something occurred at least once

HI LinusH, thank you for your response. I tried your code and it created a table but returned all 0 values to the HTID. Other ideas?

 

Also,I think you are right about storing the indicator variable onthe last id row, so what is a better alternative? Thanks!

Solution
‎02-15-2016 03:18 AM
Super User
Posts: 10,044

Re: DO loop to indicate if something occurred at least once

data have;
input ID   visit   method  $;
cards;
1      1       inj        
1      2       inj      
1      3       inj
1      4       .
1      5       iud
2      1      ocp
2      2      ocp
2      3      .
3      1      iud
3      2      iud
 ;
 run;
 data want;
do until(last.id);
  set have;
  by id;
  if method='iud' then found=1; 
end;
do until(last.id);
  set have;
  by id;
  if  last.id then tried=ifn(found=1,1,0); output;
end;
drop found;
run;
Occasional Contributor
Posts: 17

Re: DO loop to indicate if something occurred at least once

thanks, Ksharp, this worked!
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 412 views
  • 1 like
  • 3 in conversation