DATA Step, Macro, Functions and more

assigning a random index-date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 134
Accepted Solution

assigning a random index-date

I have the following dataset 

group ptid dx_dt index_dt
0 1  1/1/2005 .
0  2 2/2/2005 .
0  3 1/2/2007 .
0 4  3/3/2006 .
1 5 1/24/2005  2/24/2005
1 6 1/6/2005   1/15/2005
1 7 4/2/2007   4/29/2007
1 8 3/3/2006   3/9/2006

I have the following data with patient in the group "0" without index_date but only diagnosis date and those in group "1" with index_Date and diagnosis date. I want to generate a random index date for group "0" based on the information obtained from group "1"

any help is much appreciated 


Accepted Solutions
Solution
‎01-12-2018 07:05 PM
Super User
Posts: 22,850

Re: assigning a random index-date

Posted in reply to lillymaginta

I explicitly noted that issue in the comments and am leaving that up to you Smiley Happy

 

You could either: 

1. If less than 0, set to 0 and assume same day

2. Use a different distribution that won't produce negative numbers

3. Repeatedly generate random numbers until you get a non-negative value

4. Some other options that I likely haven't thought of that makes sense given your data....

View solution in original post


All Replies
Super User
Posts: 22,850

Re: assigning a random index-date

Posted in reply to lillymaginta

Does this need to scale beyond your data here? Are there multiple groups and such that need to be accounted for?

 

In this scenario it's relatively simple but if it needs to be generalized it would help to know that ahead of time.

Frequent Contributor
Posts: 134

Re: assigning a random index-date

Hi Reeza, there are only two groups, one with index-date and the control (without index-date). I just want the randomly generated index-date for the control group to be as close as possible to group (1) taking into account that all of the randomly generated date (index-date) for the control group should occur after the diagnosis-date for that particular id. 

 

Super User
Posts: 22,850

Re: assigning a random index-date

Posted in reply to lillymaginta

taking into account that all of the randomly generated date (index-date) for the control group should occur after the diagnosis-date for that particular id. 

 

This wasn't included in your original criteria.

Are you looking to replicate the duration, ie time from diagnosis to index date, or replicate the dates. 

 

 

Frequent Contributor
Posts: 134

Re: assigning a random index-date

I want to the index-date of the controls to replicate the duration between the diagnosis-dt and index-date of group "1". 

Super User
Posts: 22,850

Re: assigning a random index-date

Posted in reply to lillymaginta

I would assume a normal or some type of distribution then on the duration. 

So I would calculate the duration for the data I have, and the mean and standard deviation.

 

Then simulate a value using a Random Normal distribution and add it to the date you have. 

 

Here's an example using Normal distribution. You'll need to check that its after the dx_dt or account for that condition.

 

delete_random_simulation.JPG

Frequent Contributor
Posts: 134

Re: assigning a random index-date

Thank you Reeza but the code produce duration with negative value meaning some of the index_date happen before the dx_dt, can you help correct the error 

 data have;
input group patid dx_dt: mmddyy10. index_dt: mmddyy10. ;
format dx_dt index_dt mmddyy10.;
cards; 
0 1  1/1/2005 .
0  2 2/2/2005 .
0  3 1/2/2007 .
0 4  3/3/2006 .
1 5 1/24/2005  2/24/2005
1 6 1/6/2005   1/15/2005
1 7 4/2/2007   4/29/2007
1 8 3/3/2006   3/9/2006

;;;;
   run;
data have;
set have;
duration= index_dt-dx_dt;run;
   proc sql noprint;
   select mean(duration), std(duration)
   into: dur_avg, :dur_std
   from have
   where not missing(duration);
   quit;

   %put &dur_avg;
   %out &dur_std;

   data want;
   set have;
   if missing(index_dt) then
   duration= rand('normal', &dur_avg, &dur_std);
   index_dt= floor(dx_dt +duration);
   format index_dt dx_dt date9.;
   run;

Solution
‎01-12-2018 07:05 PM
Super User
Posts: 22,850

Re: assigning a random index-date

Posted in reply to lillymaginta

I explicitly noted that issue in the comments and am leaving that up to you Smiley Happy

 

You could either: 

1. If less than 0, set to 0 and assume same day

2. Use a different distribution that won't produce negative numbers

3. Repeatedly generate random numbers until you get a non-negative value

4. Some other options that I likely haven't thought of that makes sense given your data....

Frequent Contributor
Posts: 134

Re: assigning a random index-date

Thank you for the prompt reply! 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 148 views
  • 2 likes
  • 2 in conversation