topic DO loop to indicate if something occurred at least once in SAS Data Management

DO loop to indicate if something occurred at least once

mcdj — Mon, 15 Feb 2016 06:54:32 GMT

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

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

LinusH — Mon, 15 Feb 2016 07:38:47 GMT

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

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

mcdj — Mon, 15 Feb 2016 07:58:38 GMT

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!

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

Ksharp — Mon, 15 Feb 2016 08:16:22 GMT

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;

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

mcdj — Mon, 15 Feb 2016 08:19:00 GMT

thanks, Ksharp, this worked!