BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ALAA1
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ALAA1
Obsidian | Level 7
Fantastic!!! your codes are exactly gave me the output what I want thank you so much

View solution in original post

9 REPLIES 9
Ksharp
Super User

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
Obsidian | Level 7

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

thank you again

ALAA1
Obsidian | Level 7
sory not to de duplicate I have to transpose the data set.
s_lassen
Meteorite | Level 14

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
Obsidian | Level 7
Fantastic!!! your codes are exactly gave me the output what I want thank you so much
s_lassen
Meteorite | Level 14

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
Obsidian | Level 7

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
Barite | Level 11

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.

________________________

- Cheers -

ALAA1
Obsidian | Level 7
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 ?

sas-innovate-white.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 2743 views
  • 0 likes
  • 4 in conversation