Hi ,
This is what my dataset looks like right now :
obs | Patient ID | LineName | StartDate | EndDate |
1 | F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 2/1/2016 | 2/11/2016 |
2 | F002F4C07392F | FOLFIRINOX | 8/19/2015 | 04/05/2016 |
3 | F002F4C07392F | Gemcitabine,Paclitaxel Protein-Bound | 4/6/2016 | 5/11/2016 |
4 | F00314B361B6A | FOLFOX | 8/22/2017 | 03/07/2018 |
5 | F00314B361B6A | Gemcitabine | 10/12/2015 | 11/27/2016 |
6 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 08/21/2017 |
But i want to see somehting like this :
The rule is that each treatment line should not be longer than the 6 months, and if it is then we break it down and mark the EndDate as StartDate +180 and the new treatment starts form StartDate +181. if there are more one type of treatment for the same patent in the duration of the 6 months, the earliest one is taken.
..
How to write this ?
obs | Patient ID | LineName | StartDate | EndDate |
1 | F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 2/1/2016 | 2/11/2016 |
2 | F002F4C07392F | FOLFIRINOX | 8/19/2015 | 1/19/2016 |
3 | F002F4C07392F | FOLFIRINOX | 1/20/2016 | 4/5/2016 |
4 | F002F4C07392F | Gemcitabine,Paclitaxel Protein-Bound | 4/6/2016 | 5/11/2016 |
5 | F00314B361B6A | FOLFOX | 8/22/2017 | 1/22/2017 |
6 | F00314B361B6A | FOLFOX | 1/23/2017 | 3/7/2018 |
7 | F00314B361B6A | Gemcitabine | 10/12/2015 | 3/12/2016 |
8 | F00314B361B6A | Gemcitabine | 3/13/2016 | 8/13/2016 |
9 | F00314B361B6A | Gemcitabine | 8/14/2016 | 11/27/2016 |
10 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 4/28/2016 |
11 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 4/29/2016 | 9/29/2016 |
12 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 9/30/2016 | 3/2/2017 |
13 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 3/3/2016 | 8/3/2017 |
14 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 8/4/2017 | 8/21/2017 |
Hi,
I don't think your output matches the requirement you mentioned.
@manya92 wrote:
Hi ,
This is what my dataset looks like right now :
obs Patient ID LineName StartDate EndDate 1 F000678A98792 Gemcitabine,Paclitaxel Protein-Bound 2/1/2016 2/11/2016 2 F002F4C07392F FOLFIRINOX 8/19/2015 04/05/2016 3 F002F4C07392F Gemcitabine,Paclitaxel Protein-Bound 4/6/2016 5/11/2016 4 F00314B361B6A FOLFOX 8/22/2017 03/07/2018 5 F00314B361B6A Gemcitabine 10/12/2015 11/27/2016 6 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 11/28/2016 08/21/2017
But i want to see somehting like this :
The rule is that each treatment line should not be longer than the 6 months, and if it is then we break it down and mark the EndDate as StartDate +180 and the new treatment starts form StartDate +181. if there are more one type of treatment for the same patent in the duration of the 6 months, the earliest one is taken.
..
How to write this ?
obs Patient ID LineName StartDate EndDate 1 F000678A98792 Gemcitabine,Paclitaxel Protein-Bound 2/1/2016 2/11/2016 2 F002F4C07392F FOLFIRINOX 8/19/2015 1/19/2016 3 F002F4C07392F FOLFIRINOX 1/20/2016 4/5/2016 4 F002F4C07392F Gemcitabine,Paclitaxel Protein-Bound 4/6/2016 5/11/2016 5 F00314B361B6A FOLFOX 8/22/2017 1/22/2017 6 F00314B361B6A FOLFOX 1/23/2017 3/7/2018 7 F00314B361B6A Gemcitabine 10/12/2015 3/12/2016 8 F00314B361B6A Gemcitabine 3/13/2016 8/13/2016 9 F00314B361B6A Gemcitabine 8/14/2016 11/27/2016 10 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 11/28/2016 4/28/2016 11 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 4/29/2016 9/29/2016 12 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 9/30/2016 3/2/2017 13 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 3/3/2016 8/3/2017 14 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 8/4/2017 8/21/2017
How can the end data be past start date here.
Here is my approach on how I understood your requirement, if not you can change modify the code to your requirement.
DATA have;
format StartDate EndDate mmddyy10.;
infile datalines dlm=',' dsd missover;
input PatientID :$15. LineName :$50. StartDate :mmddyy10. EndDate :mmddyy10.;
datalines;
F000678A98792,Gemcitabine Paclitaxel Protein-Bound,2/1/2016,2/11/2016
F002F4C07392F,FOLFIRINOX,8/19/2015,04/05/2016
F002F4C07392F,Gemcitabine Paclitaxel Protein-Bound,4/6/2016,5/11/2016
F00314B361B6A,FOLFOX,8/22/2017,03/07/2018
F00314B361B6A,Gemcitabine,10/12/2015,11/27/2016
F00314B361B6A,Gemcitabine Paclitaxel Protein-Bound,11/28/2016,08/21/2017
;
run;
data want ;
format New_SartDate New_EndDate mmddyy10.;
set have;
if INTCK('MONTH',StartDate,EndDate)>6 then do;
do i=1 to CEIL(INTCK('MONTH',StartDate,EndDate)/6) ;
New_SartDate=StartDate;
New_EndDate=StartDate+180;
StartDate=StartDate+181;
if New_EndDate>EndDate then New_EndDate=EndDate;
output;
end;
end;
else output;
run;
can you explain me this logic.Why dividing by 6?
do i=1 to CEIL(INTCK('MONTH',StartDate,EndDate)/6) ;
I wrote this code , but it doesnt seem to work completely :
data want ;
set have ;
by PatientID ;
StartDate2 = StartDate ;
retain StartDate2 ;
format new_enddate new_startdate StartDate2 mmddyy10. ;
if (EndDate - StartDate) ge 180
then do ;
new_startdate = StartDate2 ;
new_enddate = sum(new_startdate,180);
StartDate2 = StartDate +181 ;
end;
else if (EndDate - StartDate2) lt 180 then do;
new_enddate = sum(StartDate2,180) ;
new_startdate = StartDate2 ;
end ;
run ;
RAW DATA
Obs PatientID LineName StartDate EndDate1234567891011121314151617181920
F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 02/01/2016 | 02/11/2016 |
F002F4C07392F | FOLFIRINOX | 08/19/2015 | 04/05/2016 |
F002F4C07392F | Gemcitabine,Paclitaxel Protein-Bound | 04/06/2016 | 05/11/2016 |
F00314B361B6A | Gemcitabine | 10/12/2015 | 11/27/2016 |
F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 08/21/2017 |
F00314B361B6A | FOLFOX | 08/22/2017 | 03/07/2018 |
F003E7ADEB2F8 | Gemcitabine,Paclitaxel Protein-Bound | 01/20/2017 | 02/03/2017 |
F006A77E4CDB0 | FOLFIRINOX | 07/12/2017 | 08/27/2017 |
F006A77E4CDB0 | Pembrolizumab | 08/28/2017 | 12/07/2017 |
F007499692F0D | FOLFIRINOX | 08/28/2017 | 10/31/2017 |
F007499692F0D | Gemcitabine,Paclitaxel Protein-Bound | 11/01/2017 | 03/26/2018 |
F0080CAE5E872 | Gemcitabine,Paclitaxel Protein-Bound | 02/12/2014 | 04/19/2017 |
F00C081DC6BC4 | Gemcitabine,Paclitaxel Protein-Bound | 02/17/2017 | 08/13/2017 |
F00C081DC6BC4 | Capecitabine,Gemcitabine | 08/14/2017 | 11/09/2017 |
F00CC20661EE3 | Gemcitabine,Paclitaxel Protein-Bound | 10/19/2015 | 05/03/2016 |
F00CC20661EE3 | FOLFIRINOX | 05/04/2016 | 05/09/2016 |
F00E0ED1D3143 | FOLFIRINOX | 09/21/2016 | 01/02/2017 |
F00E0ED1D3143 | Fluorouracil,Gemcitabine,Irinotecan,Leucovorin,Oxaliplatin,Paclitaxel Protein-Bound | 01/03/2017 | 03/27/2018 |
F00F92CD2DB8C | FOLFOX | 06/09/2016 | 06/23/2016 |
F01147636BCCA | Gemcitabine,Paclitaxel Protein-Bound | 08/04/2015 | 11/03/2015 |
EPISODE RULE
I want to see the values in StartDate2 to appear in new_startdate .. and tehn add 180 to them to get the new_enddate..
Obs PatientID LineName StartDate EndDate new_startdate new_enddate StartDate21234567891011121314151617181920
F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 02/01/2016 | 02/11/2016 | 02/01/2016 | 07/30/2016 | 02/01/2016 |
F002F4C07392F | FOLFIRINOX | 08/19/2015 | 04/05/2016 | 02/16/2016 | 02/15/2016 | 08/19/2015 |
F002F4C07392F | Gemcitabine,Paclitaxel Protein-Bound | 04/06/2016 | 05/11/2016 | 04/06/2016 | 10/03/2016 | 04/06/2016 |
F00314B361B6A | Gemcitabine | 10/12/2015 | 11/27/2016 | 04/10/2016 | 04/09/2016 | 10/12/2015 |
F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 08/21/2017 | 05/28/2017 | 05/27/2017 | 11/28/2016 |
F00314B361B6A | FOLFOX | 08/22/2017 | 03/07/2018 | 02/19/2018 | 02/18/2018 | 08/22/2017 |
F003E7ADEB2F8 | Gemcitabine,Paclitaxel Protein-Bound | 01/20/2017 | 02/03/2017 | 01/20/2017 | 07/19/2017 | 01/20/2017 |
F006A77E4CDB0 | FOLFIRINOX | 07/12/2017 | 08/27/2017 | 07/12/2017 | 01/08/2018 | 07/12/2017 |
F006A77E4CDB0 | Pembrolizumab | 08/28/2017 | 12/07/2017 | 08/28/2017 | 02/24/2018 | 08/28/2017 |
F007499692F0D | FOLFIRINOX | 08/28/2017 | 10/31/2017 | 08/28/2017 | 02/24/2018 | 08/28/2017 |
F007499692F0D | Gemcitabine,Paclitaxel Protein-Bound | 11/01/2017 | 03/26/2018 | 11/01/2017 | 04/30/2018 | 11/01/2017 |
F0080CAE5E872 | Gemcitabine,Paclitaxel Protein-Bound | 02/12/2014 | 04/19/2017 | 08/12/2014 | 08/11/2014 | 02/12/2014 |
F00C081DC6BC4 | Gemcitabine,Paclitaxel Protein-Bound | 02/17/2017 | 08/13/2017 | 02/17/2017 | 08/16/2017 | 02/17/2017 |
F00C081DC6BC4 | Capecitabine,Gemcitabine | 08/14/2017 | 11/09/2017 | 08/14/2017 | 02/10/2018 | 08/14/2017 |
F00CC20661EE3 | Gemcitabine,Paclitaxel Protein-Bound | 10/19/2015 | 05/03/2016 | 04/17/2016 | 04/16/2016 | 10/19/2015 |
F00CC20661EE3 | FOLFIRINOX | 05/04/2016 | 05/09/2016 | 05/04/2016 | 10/31/2016 | 05/04/2016 |
F00E0ED1D3143 | FOLFIRINOX | 09/21/2016 | 01/02/2017 | 09/21/2016 | 03/20/2017 | 09/21/2016 |
F00E0ED1D3143 | Fluorouracil,Gemcitabine,Irinotecan,Leucovorin,Oxaliplatin,Paclitaxel Protein-Bound | 01/03/2017 | 03/27/2018 | 07/03/2017 | 07/02/2017 | 01/03/2017 |
F00F92CD2DB8C | FOLFOX | 06/09/2016 | 06/23/2016 | 06/09/2016 | 12/06/2016 | 06/09/2016 |
F01147636BCCA | Gemcitabine,Paclitaxel Protein-Bound | 08/04/2015 | 11/03/2015 | 08/04/2015 | 01/31/2016 | 08/04/2015 |
I want it to look like this :
obs | Patient ID | LineName | StartDate | EndDate |
1 | F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 2/1/2016 | 7/1/2016 |
2 | F002F4C07392F | FOLFIRINOX | 8/19/2015 | 1/19/2016 |
3 | F002F4C07392F | FOLFIRINOX | 1/20/2016 | 6/20/2016 |
4 | F002F4C07392F | Gemcitabine,Paclitaxel Protein-Bound | 4/6/2016 | 5/11/2016 |
5 | F00314B361B6A | FOLFOX | 8/22/2017 | 1/22/2017 |
6 | F00314B361B6A | FOLFOX | 1/23/2017 | 3/7/2018 |
7 | F00314B361B6A | Gemcitabine | 10/12/2015 | 3/12/2016 |
8 | F00314B361B6A | Gemcitabine | 3/13/2016 | 8/13/2016 |
9 | F00314B361B6A | Gemcitabine | 8/14/2016 | 11/27/2016 |
10 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 4/28/2016 |
11 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 4/29/2016 | 9/29/2016 |
12 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 9/30/2016 | 3/2/2017 |
13 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 3/3/2016 | 8/3/2017 |
14 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 8/4/2017 | 8/21/2017 |
@manya92 wrote:
I wrote this code , but it doesnt seem to work completely :
data want ;
set have ;
by PatientID ;
StartDate2 = StartDate ;
retain StartDate2 ;
format new_enddate new_startdate StartDate2 mmddyy10. ;
if (EndDate - StartDate) ge 180
then do ;
new_startdate = StartDate2 ;
new_enddate = sum(new_startdate,180);
StartDate2 = StartDate +181 ;
end;
else if (EndDate - StartDate2) lt 180 then do;
new_enddate = sum(StartDate2,180) ;
new_startdate = StartDate2 ;
end ;
run ;
RAW DATA
Obs PatientID LineName StartDate EndDate1234567891011121314151617181920
F000678A98792 Gemcitabine,Paclitaxel Protein-Bound 02/01/2016 02/11/2016 F002F4C07392F FOLFIRINOX 08/19/2015 04/05/2016 F002F4C07392F Gemcitabine,Paclitaxel Protein-Bound 04/06/2016 05/11/2016 F00314B361B6A Gemcitabine 10/12/2015 11/27/2016 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 11/28/2016 08/21/2017 F00314B361B6A FOLFOX 08/22/2017 03/07/2018 F003E7ADEB2F8 Gemcitabine,Paclitaxel Protein-Bound 01/20/2017 02/03/2017 F006A77E4CDB0 FOLFIRINOX 07/12/2017 08/27/2017 F006A77E4CDB0 Pembrolizumab 08/28/2017 12/07/2017 F007499692F0D FOLFIRINOX 08/28/2017 10/31/2017 F007499692F0D Gemcitabine,Paclitaxel Protein-Bound 11/01/2017 03/26/2018 F0080CAE5E872 Gemcitabine,Paclitaxel Protein-Bound 02/12/2014 04/19/2017 F00C081DC6BC4 Gemcitabine,Paclitaxel Protein-Bound 02/17/2017 08/13/2017 F00C081DC6BC4 Capecitabine,Gemcitabine 08/14/2017 11/09/2017 F00CC20661EE3 Gemcitabine,Paclitaxel Protein-Bound 10/19/2015 05/03/2016 F00CC20661EE3 FOLFIRINOX 05/04/2016 05/09/2016 F00E0ED1D3143 FOLFIRINOX 09/21/2016 01/02/2017 F00E0ED1D3143 Fluorouracil,Gemcitabine,Irinotecan,Leucovorin,Oxaliplatin,Paclitaxel Protein-Bound 01/03/2017 03/27/2018 F00F92CD2DB8C FOLFOX 06/09/2016 06/23/2016 F01147636BCCA Gemcitabine,Paclitaxel Protein-Bound 08/04/2015 11/03/2015
EPISODE RULE
I want to see the values in StartDate2 to appear in new_startdate .. and tehn add 180 to them to get the new_enddate..
Obs PatientID LineName StartDate EndDate new_startdate new_enddate StartDate21234567891011121314151617181920
F000678A98792 Gemcitabine,Paclitaxel Protein-Bound 02/01/2016 02/11/2016 02/01/2016 07/30/2016 02/01/2016 F002F4C07392F FOLFIRINOX 08/19/2015 04/05/2016 02/16/2016 02/15/2016 08/19/2015 F002F4C07392F Gemcitabine,Paclitaxel Protein-Bound 04/06/2016 05/11/2016 04/06/2016 10/03/2016 04/06/2016 F00314B361B6A Gemcitabine 10/12/2015 11/27/2016 04/10/2016 04/09/2016 10/12/2015 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 11/28/2016 08/21/2017 05/28/2017 05/27/2017 11/28/2016 F00314B361B6A FOLFOX 08/22/2017 03/07/2018 02/19/2018 02/18/2018 08/22/2017 F003E7ADEB2F8 Gemcitabine,Paclitaxel Protein-Bound 01/20/2017 02/03/2017 01/20/2017 07/19/2017 01/20/2017 F006A77E4CDB0 FOLFIRINOX 07/12/2017 08/27/2017 07/12/2017 01/08/2018 07/12/2017 F006A77E4CDB0 Pembrolizumab 08/28/2017 12/07/2017 08/28/2017 02/24/2018 08/28/2017 F007499692F0D FOLFIRINOX 08/28/2017 10/31/2017 08/28/2017 02/24/2018 08/28/2017 F007499692F0D Gemcitabine,Paclitaxel Protein-Bound 11/01/2017 03/26/2018 11/01/2017 04/30/2018 11/01/2017 F0080CAE5E872 Gemcitabine,Paclitaxel Protein-Bound 02/12/2014 04/19/2017 08/12/2014 08/11/2014 02/12/2014 F00C081DC6BC4 Gemcitabine,Paclitaxel Protein-Bound 02/17/2017 08/13/2017 02/17/2017 08/16/2017 02/17/2017 F00C081DC6BC4 Capecitabine,Gemcitabine 08/14/2017 11/09/2017 08/14/2017 02/10/2018 08/14/2017 F00CC20661EE3 Gemcitabine,Paclitaxel Protein-Bound 10/19/2015 05/03/2016 04/17/2016 04/16/2016 10/19/2015 F00CC20661EE3 FOLFIRINOX 05/04/2016 05/09/2016 05/04/2016 10/31/2016 05/04/2016 F00E0ED1D3143 FOLFIRINOX 09/21/2016 01/02/2017 09/21/2016 03/20/2017 09/21/2016 F00E0ED1D3143 Fluorouracil,Gemcitabine,Irinotecan,Leucovorin,Oxaliplatin,Paclitaxel Protein-Bound 01/03/2017 03/27/2018 07/03/2017 07/02/2017 01/03/2017 F00F92CD2DB8C FOLFOX 06/09/2016 06/23/2016 06/09/2016 12/06/2016 06/09/2016 F01147636BCCA Gemcitabine,Paclitaxel Protein-Bound 08/04/2015 11/03/2015 08/04/2015 01/31/2016 08/04/2015 I want it to look like this :
obs Patient ID LineName StartDate EndDate 1 F000678A98792 Gemcitabine,Paclitaxel Protein-Bound 2/1/2016 7/1/2016 2 F002F4C07392F FOLFIRINOX 8/19/2015 1/19/2016 3 F002F4C07392F FOLFIRINOX 1/20/2016 6/20/2016 4 F002F4C07392F Gemcitabine,Paclitaxel Protein-Bound 4/6/2016 5/11/2016 5 F00314B361B6A FOLFOX 8/22/2017 1/22/2017 6 F00314B361B6A FOLFOX 1/23/2017 3/7/2018 7 F00314B361B6A Gemcitabine 10/12/2015 3/12/2016 8 F00314B361B6A Gemcitabine 3/13/2016 8/13/2016 9 F00314B361B6A Gemcitabine 8/14/2016 11/27/2016 10 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 11/28/2016 4/28/2016 11 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 4/29/2016 9/29/2016 12 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 9/30/2016 3/2/2017 13 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 3/3/2016 8/3/2017 14 F00314B361B6A Gemcitabine,Paclitaxel Protein-Bound 8/4/2017 8/21/2017
Can you please explain how can you record 10 here has an end date past your start date. Did you try the logic I provided.
In my previous code the DO LOOP will check how many sets of 180 days between startdate and enddate exists.
For example consider this record: F002F4C07392F,FOLFIRINOX,8/19/2015,04/05/2016
here number of months b/w startdate and enddate are 8(INTCK('MONTH',StartDate,EndDate)) months. So for this situation it need two
records for this record with continuous start end dates.
Need:
New_SartDate New_EndDate PatientID LineName
08/19/2015 02/15/2016 F002F4C07392F FOLFIRINOX
02/16/2016 04/05/2016 F002F4C07392F FOLFIRINOX
For this you need a do loop to iterate 2(CEIL(INTCK('MONTH',StartDate,EndDate)/6)) times with output and necessary conditions.
NOTE: This is just an approach and you can change the logic as per your requirement.
This is what i got
Obs PatientID LineName StartDate EndDate new_startdate new_enddate StartDate21234567891011121314151617181920
F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 02/01/2016 | 02/11/2016 | . | . | 02/01/2016 |
F002F4C07392F | FOLFIRINOX | 08/19/2015 | 04/05/2016 | 08/19/2015 | 02/15/2016 | 02/16/2016 |
F002F4C07392F | FOLFIRINOX | 08/19/2015 | 04/05/2016 | 02/16/2016 | 04/05/2016 | 08/15/2016 |
F002F4C07392F | Gemcitabine,Paclitaxel Protein-Bound | 04/06/2016 | 05/11/2016 | . | . | 04/06/2016 |
F00314B361B6A | Gemcitabine | 10/12/2015 | 11/27/2016 | 10/12/2015 | 04/09/2016 | 04/10/2016 |
F00314B361B6A | Gemcitabine | 10/12/2015 | 11/27/2016 | 04/10/2016 | 10/07/2016 | 10/08/2016 |
F00314B361B6A | Gemcitabine | 10/12/2015 | 11/27/2016 | 10/08/2016 | 11/27/2016 | 04/07/2017 |
F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 08/21/2017 | 11/28/2016 | 05/27/2017 | 05/28/2017 |
F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 08/21/2017 | 05/28/2017 | 08/21/2017 | 11/25/2017 |
F00314B361B6A | FOLFOX | 08/22/2017 | 03/07/2018 | 08/22/2017 | 02/18/2018 | 02/19/2018 |
F00314B361B6A | FOLFOX | 08/22/2017 | 03/07/2018 | 02/19/2018 | 03/07/2018 | 08/19/2018 |
F003E7ADEB2F8 | Gemcitabine,Paclitaxel Protein-Bound | 01/20/2017 | 02/03/2017 | . | . | 01/20/2017 |
F006A77E4CDB0 | FOLFIRINOX | 07/12/2017 | 08/27/2017 | . | . | 07/12/2017 |
F006A77E4CDB0 | Pembrolizumab | 08/28/2017 | 12/07/2017 | . | . | 08/28/2017 |
F007499692F0D | FOLFIRINOX | 08/28/2017 | 10/31/2017 | . | . | 08/28/2017 |
F007499692F0D | Gemcitabine,Paclitaxel Protein-Bound | 11/01/2017 | 03/26/2018 | . | . | 11/01/2017 |
F0080CAE5E872 | Gemcitabine,Paclitaxel Protein-Bound | 02/12/2014 | 04/19/2017 | 02/12/2014 | 08/11/2014 | 08/12/2014 |
F0080CAE5E872 | Gemcitabine,Paclitaxel Protein-Bound | 02/12/2014 | 04/19/2017 | 08/12/2014 | 02/08/2015 | 02/09/2015 |
F0080CAE5E872 | Gemcitabine,Paclitaxel Protein-Bound | 02/12/2014 | 04/19/2017 | 02/09/2015 | 08/08/2015 | 08/09/2015 |
F0080CAE5E872 | Gemcitabine,Paclitaxel Protein-Bound | 02/12/2014 | 04/19/2017 | 08/09/2015 | 02/05/2016 | 02/06/2016 |
There are still missing vaules for new_startdate and new_enddate
If the difference is not greater than 6 months then you might get missing values. To avoid this at last add
IF missing(new_startdate) and missing(new_enddate) then DO;
new_startdate=startdate;
new_enddate=enddate;
end;
This code is still not working,
I tried this and this is result i am getting .
data test_epi_rules ;
format StartDate2 new_startdate new_enddate mmddyy10. ;
set _02_pt_regimen ;
by PatientID ;
StartDate2 = StartDate ;
test=CEIL(INTCK('MONTH',StartDate,EndDate)/6) ;
if first.PatientID and (EndDate - StartDate) ge 180 then do ;
do i=1 to CEIL(INTCK('MONTH',StartDate,EndDate)/6) ;
new_startdate=StartDate2;
new_enddate=sum(StartDate2,180);
StartDate2= sum(StartDate2,181);
if new_enddate>EndDate then new_enddate=sum(new_startdate,180) ;
output;
end;
end;
else do ;
new_startdate = StartDate ;
new_enddate = sum(new_startdate,180) ;
output ;
end ;
run; /*4406*/
Obs StartDate2 new_startdate new_enddate PatientID LineName last_visit death_date MetDiagnosisDate BirthYear age VisitDate StartDate EndDate test i1234567891011121314151617181920
02/01/2016 | 02/01/2016 | 07/30/2016 | F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 02/11/2016 | . | 08/18/2015 | 1950 | 66 | 02/09/2016 | 02/01/2016 | 02/11/2016 | 0 | . |
02/16/2016 | 08/19/2015 | 02/15/2016 | F002F4C07392F | FOLFIRINOX | 05/11/2016 | 06/15/2016 | 08/03/2015 | 1958 | 57 | 08/19/2015 | 08/19/2015 | 04/05/2016 | 2 | 1 |
08/15/2016 | 02/16/2016 | 08/14/2016 | F002F4C07392F | FOLFIRINOX | 05/11/2016 | 06/15/2016 | 08/03/2015 | 1958 | 57 | 08/19/2015 | 08/19/2015 | 04/05/2016 | 2 | 2 |
04/06/2016 | 04/06/2016 | 10/03/2016 | F002F4C07392F | FOLFIRINOX | 05/11/2016 | 06/15/2016 | 08/03/2015 | 1958 | 57 | 08/19/2015 | 04/06/2016 | 05/11/2016 | 1 | . |
05/28/2017 | 11/28/2016 | 05/27/2017 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 03/07/2018 | . | 07/24/2015 | 1947 | 69 | 12/05/2016 | 11/28/2016 | 08/21/2017 | 2 | 1 |
11/25/2017 | 05/28/2017 | 11/24/2017 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 03/07/2018 | . | 07/24/2015 | 1947 | 69 | 12/05/2016 | 11/28/2016 | 08/21/2017 | 2 | 2 |
08/22/2017 | 08/22/2017 | 02/18/2018 | F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 03/07/2018 | . | 07/24/2015 | 1947 | 69 | 12/05/2016 | 08/22/2017 | 03/07/2018 | 2 | . |
01/20/2017 | 01/20/2017 | 07/19/2017 | F003E7ADEB2F8 | Gemcitabine,Paclitaxel Protein-Bound | 02/03/2017 | 02/15/2017 | 01/13/2017 | 1955 | 62 | 01/20/2017 | 01/20/2017 | 02/03/2017 | 1 | . |
07/12/2017 | 07/12/2017 | 01/08/2018 | F006A77E4CDB0 | FOLFIRINOX | 12/07/2017 | 12/15/2017 | 07/10/2017 | 1969 | 48 | 07/12/2017 | 07/12/2017 | 08/27/2017 | 1 | . |
08/28/2017 | 08/28/2017 | 02/24/2018 | F007499692F0D | FOLFIRINOX | 03/26/2018 | . | 07/28/2017 | 1961 | 56 | 08/28/2017 | 08/28/2017 | 10/31/2017 | 1 | . |
11/01/2017 | 11/01/2017 | 04/30/2018 | F007499692F0D | FOLFIRINOX | 03/26/2018 | . | 07/28/2017 | 1961 | 56 | 08/28/2017 | 11/01/2017 | 03/26/2018 | 1 | . |
04/17/2016 | 10/19/2015 | 04/16/2016 | F00CC20661EE3 | Gemcitabine,Paclitaxel Protein-Bound | 05/09/2016 | 07/15/2016 | 10/14/2015 | 1952 | 63 | 10/19/2015 | 10/19/2015 | 05/03/2016 | 2 | 1 |
10/15/2016 | 04/17/2016 | 10/14/2016 | F00CC20661EE3 | Gemcitabine,Paclitaxel Protein-Bound | 05/09/2016 | 07/15/2016 | 10/14/2015 | 1952 | 63 | 10/19/2015 | 10/19/2015 | 05/03/2016 | 2 | 2 |
05/04/2016 | 05/04/2016 | 10/31/2016 | F00CC20661EE3 | Gemcitabine,Paclitaxel Protein-Bound | 05/09/2016 | 07/15/2016 | 10/14/2015 | 1952 | 63 | 10/19/2015 | 05/04/2016 | 05/09/2016 | 0 | . |
09/21/2016 | 09/21/2016 | 03/20/2017 | F00E0ED1D3143 | FOLFIRINOX | 03/27/2018 | . | 09/13/2016 | 1955 | 61 | 09/21/2016 | 09/21/2016 | 01/02/2017 | 1 | . |
06/09/2016 | 06/09/2016 | 12/06/2016 | F00F92CD2DB8C | FOLFOX | 06/23/2016 | 07/15/2016 | 05/19/2016 | 1940 | 76 | 06/09/2016 | 06/09/2016 | 06/23/2016 | 0 | . |
08/04/2015 | 08/04/2015 | 01/31/2016 | F01147636BCCA | Gemcitabine,Paclitaxel Protein-Bound | 12/14/2015 | 12/15/2015 | 09/19/2014 | 1954 | 61 | 08/04/2015 | 08/04/2015 | 11/03/2015 | 1 | . |
11/04/2015 | 11/04/2015 | 05/02/2016 | F01147636BCCA | Gemcitabine,Paclitaxel Protein-Bound | 12/14/2015 | 12/15/2015 | 09/19/2014 | 1954 | 61 | 08/04/2015 | 11/04/2015 | 12/14/2015 | 1 | . |
07/01/2015 | 07/01/2015 | 12/28/2015 | F012DC0F325EF | Gemcitabine,Paclitaxel Protein-Bound | 07/15/2015 | 08/15/2015 | 06/18/2014 | 1938 | 76 | 07/10/2014 | 07/01/2015 | 07/15/2015 | 0 | . |
03/09/2017 | 03/09/2017 | 09/05/2017 | F01550BF09F41 | Gemcitabine,Paclitaxel Protein-Bound | 05/11/2017 | 06/15/2017 | 02/24/2017 | 1954 | 63 | 03/09/2017 | 03/09/2017 | 05/11/2017 | 1 | . |
from this raw data ;
Obs PatientID LineName last_visit death_date MetDiagnosisDate BirthYear age VisitDate StartDate EndDate12345678910
F000678A98792 | Gemcitabine,Paclitaxel Protein-Bound | 02/11/2016 | . | 08/18/2015 | 1950 | 66 | 02/09/2016 | 02/01/2016 | 02/11/2016 |
F002F4C07392F | FOLFIRINOX | 05/11/2016 | 06/15/2016 | 08/03/2015 | 1958 | 57 | 08/19/2015 | 08/19/2015 | 04/05/2016 |
F002F4C07392F | FOLFIRINOX | 05/11/2016 | 06/15/2016 | 08/03/2015 | 1958 | 57 | 08/19/2015 | 04/06/2016 | 05/11/2016 |
F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 03/07/2018 | . | 07/24/2015 | 1947 | 69 | 12/05/2016 | 11/28/2016 | 08/21/2017 |
F00314B361B6A | Gemcitabine,Paclitaxel Protein-Bound | 03/07/2018 | . | 07/24/2015 | 1947 | 69 | 12/05/2016 | 08/22/2017 | 03/07/2018 |
F003E7ADEB2F8 | Gemcitabine,Paclitaxel Protein-Bound | 02/03/2017 | 02/15/2017 | 01/13/2017 | 1955 | 62 | 01/20/2017 | 01/20/2017 | 02/03/2017 |
F006A77E4CDB0 | FOLFIRINOX | 12/07/2017 | 12/15/2017 | 07/10/2017 | 1969 | 48 | 07/12/2017 | 07/12/2017 | 08/27/2017 |
F007499692F0D | FOLFIRINOX | 03/26/2018 | . | 07/28/2017 | 1961 | 56 | 08/28/2017 | 08/28/2017 | 10/31/2017 |
F007499692F0D | FOLFIRINOX | 03/26/2018 | . | 07/28/2017 | 1961 | 56 | 08/28/2017 | 11/01/2017 | 03/26/2018 |
F00CC20661EE3 | Gemcitabine,Paclitaxel Protein-Bound | 05/09/2016 | 07/15/2016 | 10/14/2015 | 1952 | 63 | 10/19/2015 | 10/19/2015 | 05/03/2016 |
for claim F002F4C07392F i should be getting only 2 observations because the new_enddate for that patient is more than the actual end date,, that means the second claim is already covered. i shjould not be getting three observations. and also for claim F00314B361B6A i am getting 4 whereas the new_enddate for this is 11/24/2017 and the next new_startdate is 08/22/2017 whereas it should be 11/25/2017.
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.