topic Re: assigning a random index-date in SAS Programming

assigning a random index-date

lillymaginta — Fri, 12 Jan 2018 16:12:59 GMT

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

Re: assigning a random index-date

Reeza — Fri, 12 Jan 2018 16:15:31 GMT

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.

Re: assigning a random index-date

lillymaginta — Fri, 12 Jan 2018 16:23:09 GMT

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.

Re: assigning a random index-date

Reeza — Fri, 12 Jan 2018 17:30:59 GMT

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.

Re: assigning a random index-date

lillymaginta — Fri, 12 Jan 2018 17:47:40 GMT

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

Re: assigning a random index-date

Reeza — Fri, 12 Jan 2018 17:59:22 GMT

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.

Re: assigning a random index-date

lillymaginta — Fri, 12 Jan 2018 23:54:00 GMT

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;

Re: assigning a random index-date

Reeza — Sat, 13 Jan 2018 00:00:49 GMT

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

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....

Re: assigning a random index-date

lillymaginta — Sat, 13 Jan 2018 00:06:32 GMT

Thank you for the prompt reply!