BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mcdj
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

4 REPLIES 4
LinusH
Tourmaline | Level 20

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
mcdj
Obsidian | Level 7

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!

Ksharp
Super User
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;
mcdj
Obsidian | Level 7
thanks, Ksharp, this worked!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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