BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lillymaginta
Lapis Lazuli | Level 10

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

8 REPLIES 8
Reeza
Super User

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.

lillymaginta
Lapis Lazuli | Level 10

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. 

 

Reeza
Super User

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. 

 

 

lillymaginta
Lapis Lazuli | Level 10

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

Reeza
Super User

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

lillymaginta
Lapis Lazuli | Level 10

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;

Reeza
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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