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 |
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.
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.
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 _________________________________________________________________________
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.