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.
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.
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.