How to identify the 1st line , 2nd line and third line treatment?

[ALAA1]

Hi experts ,

I would like to ask for help with following issue:

ID	prescription quantity	Prescription start date	Prescription year	drug class	Prescription end date
1	112	28/02/2013	2013	METF	19/06/2013
1	112	25/04/2013	2013	METF	14/08/2013
1	112	12/06/2013	2013	METF	01/10/2013
2	56	03/11/2015	2015	METF	28/12/2015
2	56	23/12/2015	2015	METF	16/02/2016
2	56	23/02/2016	2016	METF	18/04/2016
2	56	22/03/2016	2016	DDP_4	16/05/2016
2	56	19/04/2016	2016	METF	13/06/2016
2	56	10/05/2016	2016	DDP_4	04/07/2016
2	56	30/06/2016	2016	DDP_4	24/08/2016
2	56	05/07/2016	2016	SFU	29/08/2016
3	224	17/11/2014	2014	METF	28/06/2015
3	56	17/11/2014	2014	SFU	11/01/2015
3	224	14/01/2015	2015	METF	25/08/2015
3	56	14/01/2015	2015	SFU	10/03/2015
3	224	27/02/2015	2015	METF	08/10/2015
3	224	24/04/2015	2015	METF	03/12/2015
3	224	07/05/2015	2015	METF	16/12/2015
4	28	14/03/2008	2008	SFU	10/04/2008
4	112	10/04/2008	2008	SFU	30/07/2008
4	112	19/10/2015	2015	SFU	07/02/2016
4	224	02/12/2015	2015	METF	12/07/2016
4	224	27/01/2016	2016	METF	06/09/2016
4	224	23/03/2016	2016	METF	01/11/2016
4	224	11/05/2016	2016	METF	20/12/2016
4	224	15/02/2017	2017	METF	26/09/2017
4	56	23/02/2017	2017	DDP_4	19/04/2017
4	224	11/04/2017	2017	METF	20/11/2017
4	56	20/04/2017	2017	DDP_4	14/06/2017
4	224	08/06/2017	2017	METF	17/01/2018
4	56	14/06/2017	2017	DDP_4	08/08/2017
4	224	02/08/2017	2017	METF	13/03/2018
4	56	14/08/2017	2017	DDP_4	08/10/2017
5	168	15/07/2011	2011	METF	29/12/2011
5	84	14/10/2013	2013	METF	05/01/2014
5	168	07/11/2013	2013	METF	23/04/2014
5	168	20/01/2014	2014	METF	06/07/2014
5	168	03/02/2014	2014	METF	20/07/2014
5	168	02/04/2014	2014	METF	16/09/2014
5	56	01/05/2014	2014	METF	25/06/2014
5	224	18/06/2014	2014	METF	27/01/2015
5	224	12/08/2014	2014	METF	23/03/2015
5	224	21/08/2014	2014	METF	01/04/2015
5	224	02/10/2014	2014	METF	13/05/2015
5	224	25/11/2014	2014	METF	06/07/2015
5	224	27/11/2014	2014	METF	08/07/2015
5	28	15/01/2015	2015	DDP_4	11/02/2015
5	224	16/01/2015	2015	METF	27/08/2015
5	28	12/02/2015	2015	DDP_4	11/03/2015
5	56	19/04/2016	2016	METF	13/06/2016
5	112	09/05/2016	2016	METF	28/08/2016
5	28	08/07/2016	2016	METF	04/08/2016

what I want  in output data:

ID	FIRST LINE	SECOND LINE	THIRD LINE
1	METF	-	-
2	METF	METF + DDP_4	METF+ DDP_4+SFU
3	SFU	SFU+METF	-
4	SFU	SFU+METF	SFU+METF+DDP_4
5	METF	METF+DDP_4	-

second line therapy defined as if second drug prescription overlapped with the first drug prescription ( between prescription date and prescription end).

third line therapy defined as if third drug prescription overlapped with the first and second drug prescription ( between prescription date and prescription end).

N.B. I have tried the codes here https://communities.sas.com/t5/SAS-Programming/Concomitant-medication-use/td-p/353808 

and these codeshttps://communities.sas.com/t5/SAS-Programming/Concomitant-drug-medication-use/m-p/351710#M81886 

the first I couldn't process it as my laptop crashes because those codes require a lot of memory the second did not give me the same output data set I am looking for.

Kind regards

[Ksharp]

The following code could give you a start point.

data have;
input 
ID
prescription_quantity
Prescription_start_date	: ddmmyy10.
Prescription_year
drug_class $
Prescription_end_date : ddmmyy10.;
format Prescription_start_date Prescription_end_date  ddmmyy10.;
cards;
1
112
28/02/2013
2013
METF
19/06/2013
1
112
25/04/2013
2013
METF
14/08/2013
1
112
12/06/2013
2013
METF
01/10/2013
2
56
03/11/2015
2015
METF
28/12/2015
2
56
23/12/2015
2015
METF
16/02/2016
2
56
23/02/2016
2016
METF
18/04/2016
2
56
22/03/2016
2016
DDP_4
16/05/2016
2
56
19/04/2016
2016
METF
13/06/2016
2
56
10/05/2016
2016
DDP_4
04/07/2016
2
56
30/06/2016
2016
DDP_4
24/08/2016
2
56
05/07/2016
2016
SFU
29/08/2016
3
224
17/11/2014
2014
METF
28/06/2015
3
56
17/11/2014
2014
SFU
11/01/2015
3
224
14/01/2015
2015
METF
25/08/2015
3
56
14/01/2015
2015
SFU
10/03/2015
3
224
27/02/2015
2015
METF
08/10/2015
3
224
24/04/2015
2015
METF
03/12/2015
3
224
07/05/2015
2015
METF
16/12/2015
4
28
14/03/2008
2008
SFU
10/04/2008
4
112
10/04/2008
2008
SFU
30/07/2008
4
112
19/10/2015
2015
SFU
07/02/2016
4
224
02/12/2015
2015
METF
12/07/2016
4
224
27/01/2016
2016
METF
06/09/2016
4
224
23/03/2016
2016
METF
01/11/2016
4
224
11/05/2016
2016
METF
20/12/2016
4
224
15/02/2017
2017
METF
26/09/2017
4
56
23/02/2017
2017
DDP_4
19/04/2017
4
224
11/04/2017
2017
METF
20/11/2017
4
56
20/04/2017
2017
DDP_4
14/06/2017
4
224
08/06/2017
2017
METF
17/01/2018
4
56
14/06/2017
2017
DDP_4
08/08/2017
4
224
02/08/2017
2017
METF
13/03/2018
4
56
14/08/2017
2017
DDP_4
08/10/2017
5
168
15/07/2011
2011
METF
29/12/2011
5
84
14/10/2013
2013
METF
05/01/2014
5
168
07/11/2013
2013
METF
23/04/2014
5
168
20/01/2014
2014
METF
06/07/2014
5
168
03/02/2014
2014
METF
20/07/2014
5
168
02/04/2014
2014
METF
16/09/2014
5
56
01/05/2014
2014
METF
25/06/2014
5
224
18/06/2014
2014
METF
27/01/2015
5
224
12/08/2014
2014
METF
23/03/2015
5
224
21/08/2014
2014
METF
01/04/2015
5
224
02/10/2014
2014
METF
13/05/2015
5
224
25/11/2014
2014
METF
06/07/2015
5
224
27/11/2014
2014
METF
08/07/2015
5
28
15/01/2015
2015
DDP_4
11/02/2015
5
224
16/01/2015
2015
METF
27/08/2015
5
28
12/02/2015
2015
DDP_4
11/03/2015
5
56
19/04/2016
2016
METF
13/06/2016
5
112
09/05/2016
2016
METF
28/08/2016
5
28
08/07/2016
2016
METF
04/08/2016
;

data temp;
 set have(keep=id Prescription_start_date drug_class  Prescription_end_date);
 do date=Prescription_start_date to  Prescription_end_date;
  output;
 end;
 keep id date drug_class;
 format date ddmmyy10.;
run;
proc sort data=temp nodupkey; 
by id date drug_class;
run;
data temp1;
 do until(last.date);
  set temp;
  by id date;
  length drugs $ 100;
  drugs=catx('+',drugs,drug_class);
 end;
run;
data want;
do until(last.drugs);
 set temp1;
 by id drugs notsorted;
 if first.drugs then start_date=date;
end;
 end_date=date;
 format start_date end_date ddmmyy10.;
 keep id start_date end_date drugs;
run;

[ALAA1]

thank you for your reply your codes worked well but i have added one step to de duplicate the data.

thank you again

[ALAA1]

sory not to de duplicate I have to transpose the data set.

[s_lassen]

Here is a possible solution:

data want; 
  set have;
  by id;
  length FIRST_LINE SECOND_LINE THIRD_LINE $20;
  retain FIRST_LINE SECOND_LINE THIRD_LINE;
  if first.id then do;
    FIRST_LINE=drug_class;
    SECOND_LINE='-';
    THIRD_LINE='-';
    end;
  else do;
    if drug_class ne FIRST_LINE  then do;
      if countw(SECOND_LINE)<2 then
        SECOND_LINE=catx('+',FIRST_LINE,drug_class);
      else if scan(SECOND_LINE,2,'+') ne drug_class and countw(THIRD_LINE)<3 then
        THIRD_LINE=catx('+',SECOND_LINE,drug_class);
      end;
    end;
  if last.id;
run;

[ALAA1 · Accepted solution]

Fantastic!!! your codes are exactly gave me the output what I want thank you so much

[s_lassen]

Great! But you marked your answer to my solution as the solution, instead of my actual solution 😣 - I would be very grateful if you mark the actual solution (and so would probably others, as it clearly shows what a possible solution can be). I do not know if it is possible to change the solution assignment later on (never tried), but if you can, please do.

[ALAA1]

I clicked it by mistake I thought I have chose the right answer .. if anyone have an idea how to solve this issue or to contact who to solve this issue I'll be grateful..

[Oligolas]

Hi,

could you please check and clarify the results you posted considering this condition:

third line therapy defined as if third drug prescription overlapped with the first and second drug prescription ( between prescription date and prescription end).

---

at id=2, SUF never overlaps the METF drug intake so it shouldn't appear in "third_line"

at first view none of the third_line drugs do overlap with the first AND the second.

[ALAA1]

dear Oligolas,
you're right I did not explain it well
I meant the can be together at what one point of time did you get it ?