Pyrite | Level 9

## creating a cummulative variable based on dates

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: creating a cummulative variable based on dates

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.

3 REPLIES 3
Super User

## Re: creating a cummulative variable based on dates

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.

Super User

## Re: creating a cummulative variable based on dates

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
_________________________________________________________________________
```

Pyrite | Level 9

## Re: creating a cummulative variable based on dates

Both codes work, thank you both so much! Is it possible to accept two responses as the solution?
Discussion stats
• 3 replies
• 458 views
• 2 likes
• 3 in conversation