Hi All,
I am facing the problem in assigning start and end date in each drug usage in the following data set, I don't know how should I do this, please help me on this,
data Mediciene;
input PTID $ 1-8 Dayssup 10-11 @13 Drug_dt date11. Drug $23-36;
format Drug_dt date9.;
datalines;
21847081 28 16-Oct-08 dexamethasone
21847081 28 04-Dec-08 dexamethasone
21847081 30 30-Dec-08 dexamethasone
21847081 28 01-Feb-09 dexamethasone
21847081 30 10-Aug-10 dexamethasone
21847081 30 24-Sep-10 dexamethasone
21847081 30 06-Nov-10 dexamethasone
21847081 28 07-Jun-12 dexamethasone
21847081 28 06-Jul-12 dexamethasone
21847081 28 14-Aug-12 dexamethasone
21847081 28 14-Sep-12 dexamethasone
21847081 28 28-Oct-12 dexamethasone
21847081 28 06-Dec-02 dexamethasone
21847081 28 18-Jan-13 dexamethasone
21847081 21 24-Sep-10 Lenalidomide
21847081 21 20-Oct-10 Lenalidomide
21847081 21 18-Nov-10 Lenalidomide
21847081 21 16-Dec-10 Lenalidomide
21847081 21 18-Jan-11 Lenalidomide
21847081 21 10-Feb-11 Lenalidomide
21847081 21 14-Mar-11 Lenalidomide
21847081 21 01-Jun-12 Lenalidomide
21847081 21 26-Jun-12 Lenalidomide
21847081 21 19-Jul-12 Lenalidomide
21847081 21 23-Aug-12 Lenalidomide
21847081 21 18-Sep-12 Lenalidomide
21847081 21 19-Oct-12 Lenalidomide
21847081 21 14-Nov-12 Lenalidomide
21847081 21 13-Dec-12 Lenalidomide
21847081 21 14-Jan-13 Lenalidomide
21847081 90 12-Mar-13 prednisolone
21847081 89 25-Jun-13 prednisolone
21847081 28 15-Oct-08 thalidomide
21847081 28 04-Dec-08 thalidomide
21847081 28 05-Jan-09 thalidomide
21847081 28 28-Jan-09 thalidomide
21847081 28 02-Mar-09 thalidomide
21847081 28 27-Mar-09 thalidomide
;
Business Logic:- Treatment period for a drug would be the period from the first date of use to the last date of use. The latter is the date when there is at least a 60/30/90 day gap(Here the gap is 90 days for dexamethasone and 60 days for all other drugs) between the end date of usage of one claim (Start date + days supply) and the claim date of the next claim of the same drug.
Example -
PTID Drug_dt Drug Startdate + days supply Gap
21847081 28 16-Oct-08 dexamethasone 12-nov-08 22
21847081 28 04-Dec-08 dexamethasone 31-dec-08 -1
21847081 30 30-Dec-08 dexamethasone 28-jan-09 4
21847081 28 01-Feb-09 dexamethasone 28-feb-09 528
21847081 30 10-Aug-10 dexamethasone 08-sep-10 16
For dexamethasone in above table, the gap days is less than 90 till row 4. so it will become a single regimn containing dexamethasone with start date of 16-Oct-08 and end date of 28-Feb-09. And there will be a different start date of 10-Aug-10 as there is gap of more than 90 days. Applying the same logic in all data, we get final table (desired Output).
I want to create desired output like this(Below), but I am unabling to do so,
Output data Set (Desired Output):-
PTID Dayssup Drug Stdt enddt
21847081 30 dexamethasone 16Oct2008 28Feb2009
21847081 28 dexamethasone 10Aug2010 05Dec2010
21847081 28 dexamethasone 07jun2012 14feb2013
21847081 21 Lenalidomide 24sep2010 03apr2011
21847081 21 Lenalidomide 01jun2012 03feb2013
21847081 89 prednisolone 12mar2013 21sep2013
21847081 28 thalidomide 15Oct2008 23apr2009
There seems to be a mistake in the data provided, the year in in line
21847081 28 06-Dec-02 dexamethasone
should be (20)12, not (20)02, right?
If you want to get a solution yourself, you will want to look for documentation explaining
The following solution needs further testing before i would call it stable.
data work.want;
if eof = 0 then do;
set work.Mediciene(firstobs= 2 keep= Drug_dt rename=(Drug_dt=NextStart)) end=eof;
end;
set work.Mediciene;
by ptid Drug notsorted;
length StartDate EndDate 8 outmarker 8;
format StartDate EndDate date9.;
retain StartDate;
EndDate = drug_dt + dayssup -1;
if first.Drug then do;
NextStart = EndDate;
StartDate = drug_dt;
end;
Gap = NextStart - EndDate;
outmarker = 0;
if lowcase(Drug) = 'dexamethasone' then do;
if Gap > 90 then do;
outmarker = 1;
end;
end;
else do;
if Gap > 60 then do;
outmarker = 2;
end;
end;
if last.drug then do;
outmarker = 3;
end;
if outmarker > 0 then do;
output;
StartDate = NextStart;
end;
drop outmarker Drug_dt Gap;
run;
Post test data in the form of a datastep.
Post required output in the body of the text of the post.
Explain the logic between the two.
Show what code you have written and where you are stuck.
Hi,
I have edited the code as you have asked, I don't know what code should I write so that I can get a desired output, can you help me on this?
And now you have to describe the logic linking both datasets.
Hi,
Now I have defined the logic between the both the data sets, have a look and help me on this.
Business Logic:- Treatment period for a drug would be the period from the first date of use to the last date of use. The latter is the date when there is at least a 60/30/90 day gap between the end date of usage of one claim and the claim date of the next claim of the same drug
But we don't know what this means: "at least a 60/30/90 day gap between the end date of usage of one claim and the claim date of the next claim of the same drug". What is a 60/30/90 day gap? We know what a 60 day gap mans. We know what a 30 day gap means. We know what a 90 day gap means. We don't know what a 60/30/90 day gap is.
Does the variable DAYSSUP have any role to play here? If so, explain.
Hi,
I have uploaded the whole case study with all the details, can you please help me on this!
I think this will do it. Note: I have fixed a typo in your original data. Assumes data is sorted by patient and drug and drug_dt.
data Mediciene;
input PTID $ 1-8 Dayssup 10-11 @13 Drug_dt date11. Drug $23-36;
end_of_supply=intnx('days',drug_dt,dayssup)-1;
prev_drug=lag(drug);
prev_end_of_supply=lag(end_of_supply);
gap=drug_dt-prev_end_of_supply;
if drug^=prev_drug then group=0;
if drug=prev_drug and ((drug='dexamethasone' and gap>90) or
(drug^='dexamethasone' and gap>60)) then group+1;
format Drug_dt end_of_supply date9.;
datalines;
21847081 28 16-Oct-08 dexamethasone
21847081 28 04-Dec-08 dexamethasone
21847081 30 30-Dec-08 dexamethasone
21847081 28 01-Feb-09 dexamethasone
21847081 30 10-Aug-10 dexamethasone
21847081 30 24-Sep-10 dexamethasone
21847081 30 06-Nov-10 dexamethasone
21847081 28 07-Jun-12 dexamethasone
21847081 28 06-Jul-12 dexamethasone
21847081 28 14-Aug-12 dexamethasone
21847081 28 14-Sep-12 dexamethasone
21847081 28 28-Oct-12 dexamethasone
21847081 28 06-Dec-12 dexamethasone
21847081 28 18-Jan-13 dexamethasone
21847081 21 24-Sep-10 Lenalidomide
21847081 21 20-Oct-10 Lenalidomide
21847081 21 18-Nov-10 Lenalidomide
21847081 21 16-Dec-10 Lenalidomide
21847081 21 18-Jan-11 Lenalidomide
21847081 21 10-Feb-11 Lenalidomide
21847081 21 14-Mar-11 Lenalidomide
21847081 21 01-Jun-12 Lenalidomide
21847081 21 26-Jun-12 Lenalidomide
21847081 21 19-Jul-12 Lenalidomide
21847081 21 23-Aug-12 Lenalidomide
21847081 21 18-Sep-12 Lenalidomide
21847081 21 19-Oct-12 Lenalidomide
21847081 21 14-Nov-12 Lenalidomide
21847081 21 13-Dec-12 Lenalidomide
21847081 21 14-Jan-13 Lenalidomide
21847081 90 12-Mar-13 prednisolone
21847081 89 25-Jun-13 prednisolone
21847081 28 15-Oct-08 thalidomide
21847081 28 04-Dec-08 thalidomide
21847081 28 05-Jan-09 thalidomide
21847081 28 28-Jan-09 thalidomide
21847081 28 02-Mar-09 thalidomide
21847081 28 27-Mar-09 thalidomide
;
proc summary nway data=mediciene;
class ptid drug group;
var drug_dt end_of_supply;
output out=want min(drug_dt)=stdt max(end_of_supply)=enddt;
run;
There seems to be a mistake in the data provided, the year in in line
21847081 28 06-Dec-02 dexamethasone
should be (20)12, not (20)02, right?
If you want to get a solution yourself, you will want to look for documentation explaining
The following solution needs further testing before i would call it stable.
data work.want;
if eof = 0 then do;
set work.Mediciene(firstobs= 2 keep= Drug_dt rename=(Drug_dt=NextStart)) end=eof;
end;
set work.Mediciene;
by ptid Drug notsorted;
length StartDate EndDate 8 outmarker 8;
format StartDate EndDate date9.;
retain StartDate;
EndDate = drug_dt + dayssup -1;
if first.Drug then do;
NextStart = EndDate;
StartDate = drug_dt;
end;
Gap = NextStart - EndDate;
outmarker = 0;
if lowcase(Drug) = 'dexamethasone' then do;
if Gap > 90 then do;
outmarker = 1;
end;
end;
else do;
if Gap > 60 then do;
outmarker = 2;
end;
end;
if last.drug then do;
outmarker = 3;
end;
if outmarker > 0 then do;
output;
StartDate = NextStart;
end;
drop outmarker Drug_dt Gap;
run;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.