Create dummies when condition is met between diagnosis date and any intervention date

[misterlas]

Hi,

I have a large dataset in the following format.

identifiant	Date_diagnostic	Date1	Date2	Date3	Date4
1	2012-03-12	2010-02-13	2012-02-09	2013-08-15	2016-08-16
2	2009-08-23	2006-07-13	2009-09-14	2010-08-15	2013-08-16

I would like to be able to create dummies under different conditions

Prior_2years = 1 if any intervention date occured within  2 years prior the diagnostic date

Ant_1year = 1 if any intervention date occured within  1 year prior the diagnostic date

within_month = 1 any intervention date occured within a month before or after diagnostic date.

after_1year = 1 if any intervention date occured within  1 year after the diagnostic date

after_2year = 1 if any intervention date occured within  2 years after the diagnostic date

The output should look like:

identifiant	Date_diagnostic	Date1	Date2	Date3	Date4	Antérieur_2ans	an avant_1	dans_mois	après_1an	après_2ans
1	2012-03-12	2010-02-13	2012-02-09	2013-08-15	2016-08-16	1	1	0	0	1
2	2009-08-23	2006-07-13	2009-09-14	2010-08-15	2013-08-16	0	0	1	1	1

In the output above, for ID2, for example, date2 is within a month after diagnostic, thus within_month = 1. Likewise date2 ou date3 are within  2 years after diagnosis date, so after_year=1 et after_2year = 1.

I have tried the following code found in another thread.

data want ;

set have;

array d (*) date :;

do i = 1 pour dim(d);

d1 = (-1 le intck('month',date_diag,d(i)) le 1); if d1 = 1 then leave ; *e.i. within a month ;

d2 = (-12 le intck('month',date_diag,d(i)) lt -1); if d2 = 1 then leave ; *e.i bewteen 1 and 12 month prior diagnosis ;

.

.

end; run;;

I will appreciate your help,

thanks in advance,

[sbxkoenk]

Hello,

All my indicators are on 1.

Can you check and specify better what you want exactly?

data have;
input identifiant Date_diagnostic Date1 Date2 Date3 Date4;
informat Date: YYMMDD10.;
format   Date: date9.;
datalines;
1	2012-03-12	2010-02-13	2012-02-09	2013-08-15	2016-08-16
2	2009-08-23	2006-07-13	2009-09-14	2010-08-15	2013-08-16
;
run;

data want(drop=i);
 set have;
 array MyDate{4} Date1-Date4;
 Prior_2years=0; Ant_1year=0; within_month=0; after_2year=0; after_1year=0;
 do i = 1 to dim(MyDate);
  if INTCK('YEAR' ,MyDate(i),Date_diagnostic,'CONTINUOUS') < 2 then Prior_2years = 1;
  if INTCK('YEAR' ,MyDate(i),Date_diagnostic,'CONTINUOUS') < 1 then Ant_1year    = 1; 
  if INTCK('MONTH',MyDate(i),Date_diagnostic,'CONTINUOUS') < 1 then within_month = 1;
  if INTCK('YEAR' ,Date_diagnostic,MyDate(i),'CONTINUOUS') < 2 then after_2year  = 1; 
  if INTCK('YEAR' ,Date_diagnostic,MyDate(i),'CONTINUOUS') < 1 then after_1year  = 1;
 end;
run;
/* end of program */

Cheers,

Koen

[misterlas]

Thanks for the answer.
Basically what I want is to be able to fill out dummies if any date, from diagnosis date, falls within a specified range. Looking at ID2, diagnosis date is 2009-08-23, 2 years prior that date is 2007-08-24, thus prior_2years = 0, prior_1year = 0 as no date fall within that interval (2007/08-24 -2009/08/23). On the other side, date2 or date3 fall wihin the range of 2 years after diagnosis (2009/08/23-2011/08/22), thus after_1year is 1 and so after_2years. Within_month is 1 because of date2.
Hope I made myself clear

[sbxkoenk · Accepted solution]

OK. Clear.

Use this code as the basis for your final program:

data have;
input identifiant Date_diagnostic Date1 Date2 Date3 Date4;
informat Date: YYMMDD10.;
format   Date: date9.;
datalines;
1	2012-03-12	2010-02-13	2012-02-09	2013-08-15	2016-08-16
2	2009-08-23	2006-07-13	2009-09-14	2010-08-15	2013-08-16
;
run;

data want(drop=i);
 LENGTH identifiant Date_diagnostic Date1-Date4 8
        Prior_2years /* Ant_1year within_month after_2year after_1year */ 8;
 set have;
 by identifiant;
 array MyDate{4} Date1-Date4;
 array DistaM{4} Distance_in_M1-Distance_in_M4;
 Prior_2years=0; /* Ant_1year=0; within_month=0; after_2year=0; after_1year=0; */
 do i = 1 to dim(MyDate);
  DistaM(i) = INTCK('MONTH' ,Date_diagnostic,MyDate(i),'CONTINUOUS');
 end;
 do i = 1 to dim(DistaM);
  if DistaM(i) > -24 and DistaM(i) < 0 then Prior_2years = 1;
 end;
run;
/* end of program */

Maybe you want to use the DAY interval instead of the MONTH interval??

Use this code to experiment and see what fits best:

data _NULL_;
 date1 = '14AUG2020'd;
 date2 = '10SEP2020'd;
 date3 = '28JUL2020'd;
 distance_in_YEARS_d1d2  = intck('YEAR' ,date1,date2,'CONTINUOUS');
 distance_in_YEARS_d1d3  = intck('YEAR' ,date1,date3,'CONTINUOUS');
 distance_in_MONTHS_d1d2 = intck('MONTH',date1,date2,'CONTINUOUS');
 distance_in_MONTHS_d1d3 = intck('MONTH',date1,date3,'CONTINUOUS');
 distance_in_DAYS_d1d2   = intck('DAY'  ,date1,date2,'CONTINUOUS');
 distance_in_DAYS_d1d3   = intck('DAY'  ,date1,date3,'CONTINUOUS');
 PUT distance_in_YEARS_d1d2=;
 PUT distance_in_YEARS_d1d3=;
 PUT distance_in_MONTHS_d1d2=;
 PUT distance_in_MONTHS_d1d3=;
 PUT distance_in_DAYS_d1d2=;
 PUT distance_in_DAYS_d1d3=;
run;
/* end of program */

Koen

[sbxkoenk]

Also have a look in the documentation to know about the difference between:

• method = CONTINUOUS and
• method = DISCRETE

in the INTCK function.

[misterlas]

Thanks Koen,
I works,
Much appreciated