BookmarkSubscribeRSS Feed
manya92
Fluorite | Level 6

%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 :- 

StartDateEndDate
11/28/20168/21/2017
8/22/20173/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   

F00314B361B6A11/28/201608/21/2017Gemcitabine,Paclitaxel Protein-Bound11/28/201605/27/2017..............
 F00314B361B6A11/28/201608/21/2017Gemcitabine,Paclitaxel Protein-Bound11/28/201605/27/201705/28/201711/24/2017

 

11/28/201605/27/2017
05/28/201711/24/2017
11/25/201705/24/2018

I should be getting these dates

for three episodes. i am getting the first two but not the third

 

 

2 REPLIES 2
ballardw
Super User

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.

manya92
Fluorite | Level 6

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

F000678A9879202/01/201602/11/2016Gemcitabine,Paclitaxel Protein-Bound02/01/201607/30/2016.............
2F002F4C07392F08/19/201504/05/2016FOLFIRINOX08/19/201502/15/2016.............
3F002F4C07392F08/19/201504/05/2016FOLFIRINOX..02/16/201606/15/2016...........
4F002F4C07392F04/06/201605/11/2016Gemcitabine,Paclitaxel Protein-Bound..02/16/201606/15/2016...........
5F00314B361B6A11/28/201608/21/2017Gemcitabine,Paclitaxel Protein-Bound11/28/201605/27/2017.............
6F00314B361B6A11/28/201608/21/2017Gemcitabine,Paclitaxel Protein-Bound..05/28/201711/24/2017...........
7F00314B361B6A08/22/201703/07/2018FOLFOX..05/28/201711/24/2017...........
8F00314B361B6A11/28/201608/21/2017Gemcitabine,Paclitaxel Protein-Bound....05/24/2018..........
9F00314B361B6A08/22/201703/07/2018FOLFOX....05/24/2018..........
10F003E7ADEB2F801/20/201702/03/2017Gemcitabine,Paclitaxel Protein-Bound01/20/201702/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*/

 

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 701 views
  • 0 likes
  • 2 in conversation