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
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....
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.
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.
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.
I want to the index-date of the controls to replicate the duration between the diagnosis-dt and index-date of group "1".
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.
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;
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....
Thank you for the prompt reply!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.