BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Banke
Pyrite | Level 9

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
Kurt_Bremser
Super User

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.

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Super User Tom
Super User

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
Pyrite | Level 9
Both codes work, thank you both so much! Is it possible to accept two responses as the solution?

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 3 replies
  • 458 views
  • 2 likes
  • 3 in conversation