creating a cummulative variable based on dates

[Banke]

Hello all,

I found a solved question relating to identifying gaps in therapy (https://communities.sas.com/t5/SAS-Programming/Data-cleaning-for-Cox-regression-analysis/td-p/730972).

I have a similar problem but I don't know how to:

1. create the days and days_int variables.

2. censor patients whose dose changed.

I appreciate your assistance. Thank you

ID	Dose	Med_start_date	Days	Days_int
1	2	01JAN2015	0	.
1	4	16JAN2015	15	15
2	2	01JAN2010	0	.
2	4	11APR2010	100	100
3	8	01JAN2012	0	.
3	16	31JAN2012	30	30
3	24	10MAY2012	130	100
4	8	01MAR2017	0	.
4	8	30APR2017	60	60
4	8	29JUN2017	120	60
4	16	28AUG2017	180	60
4	16	27OCT2017	240	60
5	8	01FEB2018	0	.
5	8	02APR2018	60	60
5	16	01JUN2018	120	60
5	16	21JUL2018	170	50
5	32	20AUG2018	200	30
5	32	18NOV2018	300	90
6	1	01JAN2011	0	.
6	2	20FEB2011	50	50
6	2	21APR2011	110	60
6	4	20JUN2011	170	60
6	6	18SEP2011	260	90
7	8	01JAN2019	0	.
7	16	28OCT2019	300	300

[Kurt_Bremser · Accepted solution]

I take it that days is the number of days since the therapy started, and days_int the number since the last dose?

data want;
set have;
by id;
retain days;
days_int = dif(med_start_date);
if first.id
then do;
  days = 0;
  days_int = .;
end;
days + days_int;
dose_change = (not first.id and lag(dose) ne dose);
run;

Untested, posted from my tablet.

[Tom]

First let's convert your LISTING back into a DATASET.  Let's rename your OUTPUT variables so the code we write can use the real target variable names.

data have;
  input ID Dose Med_start_date :date. _Days _Days_int ;
  format Med_start_date date9.;
cards;
1  2 01JAN2015 0 .
1  4 16JAN2015 15 15
2  2 01JAN2010 0 .
2  4 11APR2010 100 100
3  8 01JAN2012 0 .
3 16 31JAN2012 30 30
3 24 10MAY2012 130 100
4  8 01MAR2017 0 .
4  8 30APR2017 60 60
4  8 29JUN2017 120 60
4 16 28AUG2017 180 60
4 16 27OCT2017 240 60
5  8 01FEB2018 0 .
5  8 02APR2018 60 60
5 16 01JUN2018 120 60
5 16 21JUL2018 170 50
5 32 20AUG2018 200 30
5 32 18NOV2018 300 90
6  1 01JAN2011 0 .
6  2 20FEB2011 50 50
6  2 21APR2011 110 60
6  4 20JUN2011 170 60
6  6 18SEP2011 260 90
7  8 01JAN2019 0 .
7 16 28OCT2019 300 300
;

First sort by ID and date but process by ID and DOSE so that SAS will flag when the DOSE changes.  It is probably easier to just remember the first date to calculate the DAYS variable.  For the DAYS_INT variable you can use the DIF() function.  You will just need to ignore the value generated when you start a new ID.  Your CENSOR request just sounds like you want to flag the first DOSE change.

data want;
  set have;
  by id dose notsorted;
  format day0 date9.;
  retain day0 dose0 censor ;
  days_int = dif(med_start_date);
  if first.id then do; day0 = Med_start_date ; dose0=dose ;  days_int=.; censor=0; end;
  days = med_start_date - day0 ;
  if first.dose and not first.id then censor=1;
run;

Result:

                     Med_start_             _Days_
Obs    ID    Dose       date       _Days      int          day0    dose0    censor    days_int    days

  1     1      2     01JAN2015        0         .     01JAN2015      2         0           .         0
  2     1      4     16JAN2015       15        15     01JAN2015      2         1          15        15
  3     2      2     01JAN2010        0         .     01JAN2010      2         0           .         0
  4     2      4     11APR2010      100       100     01JAN2010      2         1         100       100
  5     3      8     01JAN2012        0         .     01JAN2012      8         0           .         0
  6     3     16     31JAN2012       30        30     01JAN2012      8         1          30        30
  7     3     24     10MAY2012      130       100     01JAN2012      8         1         100       130
  8     4      8     01MAR2017        0         .     01MAR2017      8         0           .         0
  9     4      8     30APR2017       60        60     01MAR2017      8         0          60        60
 10     4      8     29JUN2017      120        60     01MAR2017      8         0          60       120
 11     4     16     28AUG2017      180        60     01MAR2017      8         1          60       180
 12     4     16     27OCT2017      240        60     01MAR2017      8         1          60       240
 13     5      8     01FEB2018        0         .     01FEB2018      8         0           .         0
 14     5      8     02APR2018       60        60     01FEB2018      8         0          60        60
 15     5     16     01JUN2018      120        60     01FEB2018      8         1          60       120
 16     5     16     21JUL2018      170        50     01FEB2018      8         1          50       170
 17     5     32     20AUG2018      200        30     01FEB2018      8         1          30       200
 18     5     32     18NOV2018      300        90     01FEB2018      8         1          90       290
 19     6      1     01JAN2011        0         .     01JAN2011      1         0           .         0
 20     6      2     20FEB2011       50        50     01JAN2011      1         1          50        50
 21     6      2     21APR2011      110        60     01JAN2011      1         1          60       110
 22     6      4     20JUN2011      170        60     01JAN2011      1         1          60       170
 23     6      6     18SEP2011      260        90     01JAN2011      1         1          90       260
 24     7      8     01JAN2019        0         .     01JAN2019      8         0           .         0
 25     7     16     28OCT2019      300       300     01JAN2019      8         1         300       300

Note that one of your expect values of DAYS is wrong.

proc compare data=want;
 id id med_start_date;
 var days days_int;
 with _days _days_int;
run;

Variables with Unequal Values

Variable  Type  Len   Compare    Len  Ndif   MaxDif

days      NUM     8   _Days        8     1   10.000



Value Comparison Results for Variables

_________________________________________________________________________
                          ||       Base    Compare
      ID  Med_start_date  ||       days      _Days      Diff.     % Diff
 _______  ______________  ||  _________  _________  _________  _________
                          ||
       5  18-NOV-2018     ||   290.0000   300.0000    10.0000     3.4483
_________________________________________________________________________

[Banke]

Both codes work, thank you both so much! Is it possible to accept two responses as the solution?