%macro do_loop ;
%do i = 1 %to 7 ;
%let c_ep =%scan(2*3*4*5*6*7*8,&i.,*); /*c_ep means current episode, pr_ep means previous episdose*/
%let pr_ep =%scan(1*2*3*4*5*6*7,&i.,*); /*ALTERBATIVELY WE CAN ALSO WRITE %EVAL (&J.-1)*/
/*VAR = PATIENTID EPISODE_START_1 EPISODE_END_1 STARTDATE ENDDATE LINENAME DEATH_DATE */
proc sql ;
create table _02_epi_&c_ep. as
select b.PatientID , a.episode_start_1, a.episode_end_1,b.StartDate, b.EndDate, b.LineName, a.death_date, a.age
from _02_epi_&pr_ep. as a
inner join _02_pt_regimen as b
on a.PatientID=b.PatientID
order by b.PatientID, b.StartDate;
quit ; /*5330*/
/* WHEN PATIENT STARTS THE SECOND EPISODE */
/*NOW WE WANT TO ADD A DO LOOP THAT WILL CHANGE OUR EPISODE2_START = EPISODE1_END +1 EVERY TIME WE CALCULATE THE NEXT EPISODE,
FOR EG. EPISODE3_START = EPISODE2_END+1 AND SO ON */
/*VAR = PATIENTID EPISODE_START_1 EPISODE_END_1 STARTDATE ENDDATE LINENAME DEATH_DATE AGE */
data _02_epi_level_&c_ep.;
set _02_epi_&pr_ep. ;
format episode_start_&c_ep. episode_end_&c_ep. mmddyy10. ;
by PatientID StartDate ;
episode_start_&c_ep. = episode_end_&pr_ep. +1 ;
if (episode_start_&c_ep. le EndDate) then do ;
episode_start_&c_ep. =max(StartDate, episode_start_&c_ep.) ;
episode_end_&c_ep.= min(death_date,episode_start_&c_ep. +180) ;
output ;
end ;
if episode_end_&c_ep. = death_date then death = 1 ;
else death = 0 ;
run ;
proc sort data = _02_epi_level_&c_ep. out = _02_epi_level_s_&c_ep. ;
by PatientID episode_start_&c_ep. ;
run ;
data _02_epi_&c_ep. ;
set _02_epi_level_s_&c_ep.;
by PatientID episode_start_&c_ep. ;
if first.PatientID ;
/*where death = 0 ;*/
run ;
%end ;
%mend do_loop;
%do_loop;
I ran this code but I am still not getting three episodes for PatientID F00314B361B6A. and the StartDate and EndDate are being repeated, The actual start and EndDates for F00314B361B6A patient are :-
StartDate | EndDate |
11/28/2016 | 8/21/2017 |
8/22/2017 | 3/7/2018 |
the first StartDAte and EndDates are for Treatment x
and the second StartDate and EndDates are for treatment y
this is what i am getting episode1_start episode1_end episode2_start episode2_end
F00314B361B6A | 11/28/2016 | 08/21/2017 | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 05/27/2017 | . | . | . | . | . | . | . | . | . | . | . | . | . | . |
F00314B361B6A | 11/28/2016 | 08/21/2017 | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 05/27/2017 | 05/28/2017 | 11/24/2017 |
11/28/2016 | 05/27/2017 |
05/28/2017 | 11/24/2017 |
11/25/2017 | 05/24/2018 |
I should be getting these dates
for three episodes. i am getting the first two but not the third
Without example input data how can we tell what may be going wrong.
Provide some example data in the form of a data step. Only include the variables that are actually involved in the issue.
Did you have an example of this code that worked BEFORE incorporating macro variables for at least one case? That is the first step.
Quite often names of variables like: episode1_start episode1_end episode2_start episode2_end indicate that perhaps part of the issue is related to the "wide" data and might be more amenable to solutions with a variable to indicate which episode and the start/end as separate records.
INPUT DATASET
1 F000678A98792 | 02/01/2016 | 02/11/2016 | Gemcitabine,Paclitaxel Protein-Bound | ||||
2 | F002F4C07392F | 08/19/2015 | 04/05/2016 | FOLFIRINOX | |||
3 | F002F4C07392F | 04/06/2016 | 05/11/2016 | Gemcitabine,Paclitaxel Protein-Bound |
4 | F00314B361B6A | 11/28/2016 | 08/21/2017 | Gemcitabine,Paclitaxel Protein-Bound |
5 | F00314B361B6A | 08/22/2017 | 03/07/2018 | FOLFOX |
6 | F003E7ADEB2F8 | 01/20/2017 | 02/03/2017 | Gemcitabine,Paclitaxel Protein-Bound |
7 | F006A77E4CDB0 | 07/12/2017 | 08/27/2017 | FOLFIRINOX |
8 | F007499692F0D | 08/28/2017 | 10/31/2017 | FOLFIRINOX |
9 | F007499692F0D | 11/01/2017 | 03/26/2018 | Gemcitabine,Paclitaxel Protein-Bound |
10 | F00CC20661EE3 | 10/19/2015 | 05/03/2016 | Gemcitabine,Paclitaxel Protein-Bound |
WHAT IT LOOKS LIKE AFTER MY CODE
F000678A98792 | 02/01/2016 | 02/11/2016 | Gemcitabine,Paclitaxel Protein-Bound | 02/01/2016 | 07/30/2016 | . | . | . | . | . | . | . | . | . | . | . | . | . |
2 | F002F4C07392F | 08/19/2015 | 04/05/2016 | FOLFIRINOX | 08/19/2015 | 02/15/2016 | . | . | . | . | . | . | . | . | . | . | . | . | . |
3 | F002F4C07392F | 08/19/2015 | 04/05/2016 | FOLFIRINOX | . | . | 02/16/2016 | 06/15/2016 | . | . | . | . | . | . | . | . | . | . | . |
4 | F002F4C07392F | 04/06/2016 | 05/11/2016 | Gemcitabine,Paclitaxel Protein-Bound | . | . | 02/16/2016 | 06/15/2016 | . | . | . | . | . | . | . | . | . | . | . |
5 | F00314B361B6A | 11/28/2016 | 08/21/2017 | Gemcitabine,Paclitaxel Protein-Bound | 11/28/2016 | 05/27/2017 | . | . | . | . | . | . | . | . | . | . | . | . | . |
6 | F00314B361B6A | 11/28/2016 | 08/21/2017 | Gemcitabine,Paclitaxel Protein-Bound | . | . | 05/28/2017 | 11/24/2017 | . | . | . | . | . | . | . | . | . | . | . |
7 | F00314B361B6A | 08/22/2017 | 03/07/2018 | FOLFOX | . | . | 05/28/2017 | 11/24/2017 | . | . | . | . | . | . | . | . | . | . | . |
8 | F00314B361B6A | 11/28/2016 | 08/21/2017 | Gemcitabine,Paclitaxel Protein-Bound | . | . | . | . | 05/24/2018 | . | . | . | . | . | . | . | . | . | . |
9 | F00314B361B6A | 08/22/2017 | 03/07/2018 | FOLFOX | . | . | . | . | 05/24/2018 | . | . | . | . | . | . | . | . | . | . |
10 | F003E7ADEB2F8 | 01/20/2017 | 02/03/2017 | Gemcitabine,Paclitaxel Protein-Bound | 01/20/2017 | 02/15/2017 | . | . | . |
WHAT IT SHOULD LOOK LIKE
Obs | PatientID | ep_st | ep_end |
1 | F000678A98792 | 02/01/2016 | 07/30/2016 |
2 | F002F4C07392F | 08/19/2015 | 02/15/2016 |
3 | F002F4C07392F | 02/16/2016 | 08/14/2016 |
4 | F00314B361B6A | 11/28/2016 | 05/27/2017 |
5 | F00314B361B6A | 05/28/2017 | 11/24/2017 |
6 | F00314B361B6A | 11/25/2017 | 05/24/2018 |
7 | F003E7ADEB2F8 | 01/20/2017 | 07/19/2017 |
8 | F006A77E4CDB0 | 07/12/2017 | 01/08/2018 |
9 | F007499692F0D | 08/28/2017 | 02/24/2018 |
10 | F00CC20661EE3 | 10/19/2015 | 04/16/2016 |
MY CODE (NOTE : - I DO NOT WANT TO CHANGE MY CODE ENTIRELY, I THINK ONLY ONE LITTLE THING IS WRONG WITH IT.
data _02_epi_level_1 ;
set _02_pt_regimen ;
format episode_start_1 episode_end_1 mmddyy10. ;
by PatientID StartDate;
if first.PatientID ;
episode_start_1= StartDate ;
episode_end_1 = min(death_date, episode_start_1 +180) ;
run ; /*2575 - ONE ROW PER PATIENT */
/*MERGING WITH THE TREATMENT LEVEL DATASET*/
/*NOW WE WANT TO ADD A DO LOOP THAT WILL CHANGE OUR EPISODE2_START = EPISODE1_END +1 EVERY TIME WE CALCULATE THE NEXT EPISODE,
FOR EG. EPISODE3_START = EPISODE2_END+1 AND SO ON */
%macro do_loop ;
%do i = 1 %to 7 ;
%let c_ep =%scan(2*3*4*5*6*7*8,&i.,*); /*c_ep means current episode, pr_ep means previous episode*/
%let pr_ep =%scan(1*2*3*4*5*6*7,&i.,*); /*ALTERBATIVELY WE CAN ALSO WRITE %EVAL (&J.-1)*/
proc sql ;
create table _02_epi_level_&c_ep. as
select b.PatientID , a.episode_start_&c_ep., a.episode_end_&c_ep. ,b.StartDate, b.EndDate, b.LineName, a.death_date, a.age
from _02_epi_level_&pr_ep. as a
inner join _02_pt_regimen as b
on a.PatientID=b.PatientID
order by b.PatientID, b.StartDate;
quit ; /*5330 -MULTIPLE ROWS PER PATIENT*/
/* WHEN PATIENT STARTS THE SECOND EPISODE */
data _02_epi_no_&c_ep.;
set _02_epi_level_&pr_ep. ;
format episode_start_&c_ep. episode_end_&c_ep. mmddyy10. ;
by PatientID StartDate ;
episode_start_&c_ep. = episode_end_&pr_ep. +1 ;
if (episode_start_&c_ep. le EndDate) then do ;
episode_start_&c_ep. =max(StartDate, episode_start_&c_ep.) ;
episode_end_&c_ep.= min(death_date,episode_start_&c_ep. +180) ;
output ;
end ;
if episode_end_&c_ep. = death_date then death = 1 ;
else death = 0 ;
run ;
proc sort data = _02_epi_no_&c_ep. out = _02_epi_no_s_&c_ep. ;
by PatientID episode_start_&c_ep. ;
run ;
data _02_epi_&c_ep. ;
set _02_epi_no_s_&c_ep.;
by PatientID episode_start_&c_ep. ;
if first.PatientID ;
/*where death = 0 ;*/
run ;
%end ;
%mend do_loop;
%do_loop;
/*6943*/
data _02_epi_all ;
set _02_epi_1 _02_epi_level_2 _02_epi_level_3 _02_epi_level_4 _02_epi_level_5 _02_epi_level_6 _02_epi_level_7 _02_epi_level_8 ;
by PatientID ;
run; /*6943*/
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.