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
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;
thank you for your reply your codes worked well but i have added one step to de duplicate the data.
thank you again
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;
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.
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..
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 -
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.
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.
Ready to level-up your skills? Choose your own adventure.