topic Re: How to create a drug course using dates. in SAS Data Management

How to create a drug course using dates.

shasank — Wed, 03 Jan 2018 20:34:42 GMT

Hello SAS community,

I need your help with the following. Any help is appreciated.

Raw data:-

ID	StartDate	Drug
1	9-May-12	A
1	10-May-12	A
1	10-May-12	B
1	30-May-12	B
1	31-May-12	A
1	1-Jun-12	A
1	2-Jun-12	B
1	30-Jun-12	C
1	3-Jul-12	A
1	27-Jul-12	A
1	28-Jul-12	D
2	17-May-12	A
2	22-May-12	B
2	25-May-12	B
2	1-Jun-12	C
2	2-Jun-12	D
2	3-Jun-12	B
3	26-Jun-12	A
3	29-Jun-12	A
3	27-Jul-12	B
3	25-Aug-12	B
4	26-Jun-12	A
4	27-Jun-12	B
4	28-Jun-12	C
4	25-Aug-12	A
4	26-Aug-12	B
4	27-Aug-12	B

I want to get the results based on line of drug therapy.

Output should look like

ID	1st line	2nd line	3rd line
1	A + B	A + C	A+D
2	A+B	B+C+D	.
3	A	B	.
4	A+B+C	.	.

1st line:

Start date: First drug
Course: Any drug in the period start date + 10 days

2nd line:

Start date: First drug NOT in 1st line
Course: Any drug in the period 3 days prior to start date of First drug NOT in 1st line to 10 days after start date

3rd line:

Start date: First drug NOT in 2nd line
Course: Any drug in the period 3 days prior to start date of First drug NOT in 2nd line to 10 days after start date.

In other words,

1st line = First drug + all drugs given within 10 days of 1st drug start date for ex. suppose that A+B+C are with in 1st 10 days.

2nd line = if person gets any other drugs than the one given in 1st line For ex. C drug is added to drug course then 2nd line is all the drugs given 3 days prior to C drug start date and 10 days after C drug start date.

3rd Line = if person gets any other drugs than the one given in 2nd or 1st line For ex. D drug is added to drug course then 3rd line is all the drugs given 3 days prior to C drug start date and 10 days after D drug start date.

and so on.

I hope I am clear about my question.

Please Help.

Re: How to create a drug course using dates.

ballardw — Wed, 03 Jan 2018 20:02:09 GMT

You should provide an example of what the outcome would look like for that example data.

"Course: Any drug in the period 3 days prior to start date to 10 days after start date" isn't really clearly defined as you have a "start date" on every record.

Re: How to create a drug course using dates.

shasank — Wed, 03 Jan 2018 20:06:36 GMT

Thank you for the suggestion .I will edit post with the reply.

Re: How to create a drug course using dates.

shasank — Wed, 03 Jan 2018 20:21:40 GMT

I took your suggestions and make necessary changes to the question. i hope this helps.

Re: How to create a drug course using dates.

novinosrin — Wed, 03 Jan 2018 21:04:24 GMT

@shasank It's an interesting problem, but I find your explanation of the logic little inadequate. Hmm part of the reason is perhaps I am not smart. Either way, Please clarify the following

q1. does every obs for each id have a line starting from line1......lineN?

q2. So the number of lines for each id varies? for example, 9 for ID 1 and 4 for ID 3?

q3 So when processing the last few obs(perhaps) for a particular id, the look up can only be prior dates for the reason there won't dates to look ahead

Re: How to create a drug course using dates.

shasank — Wed, 03 Jan 2018 21:23:07 GMT

Hello @novinosrin,
Thank you for your reply. You sound very smart based on your questions.
1) Yes every, Id will start from line 1 and will proceed to line 2 only if there is a change in the drug given. The change in the drug should trigger use to identify the course and mark it as a 2nd line and same goes for 3rd line
2) Yes, Each ID can have different number of lines.
3) Correct. The only flag we need is a change for the 1st line to flag as 2nd line and a change from 2nd line to flag as 3rd line and so on. Once we know what line it is we should identify what drugs are given during that line period.

1st line are drugs given during 1st drug startdate +10 days is 1st line drug course.
2nd line are the drugs given when we flat a new drugs not in 1st line and all the drugs given 3 days prior to it and 10 days after it is 2nd line course.
3rd line is the same as 2nd line but should be compared to 2nd line course to flag a change.

Re: How to create a drug course using dates.

novinosrin — Wed, 03 Jan 2018 21:53:27 GMT

Hmm still not clear. Anyway, can you explain the look up for the just the one row

ID	1st line	2nd line	3rd line
1	A + B	A + C	A+D

How you got the above and from where referring to your HAVE dataset?

For ID one there has to be 1st line----9th line?

Re: How to create a drug course using dates.

shasank — Wed, 03 Jan 2018 22:23:12 GMT

Hello @novinosrin,

AS per my logic. When I find a drug that is not in 2nd line therapy I will flag it as 3rd line therapy. So here D is newly added drug to the patient. Now to identify the course of therapy - I have to check 3 days before and 10 days after D was started. Drug D was started on 28th July 2012 and 1 days prior to Drug D the patient received drug A and no drugs after Drug D. Now the 3rd Cycle course is A+D or D+A.

Re: How to create a drug course using dates.

novinosrin — Wed, 03 Jan 2018 23:06:09 GMT

Hey, I am writing to apologise that I didn't get head start on your req as my brain isn't thinking well. I am off to classes at my college right now. But don;t worry, I will sincerely take a look later. Either way, You can be 100% percent, KSharp @xia keshan will fire your needed answer the moment he sees this thread even if no else responds, He is my role model. I wish I could be like him. So wait for a while!

Re: How to create a drug course using dates.

shasank — Thu, 04 Jan 2018 15:58:22 GMT

@Astounding Can you help me with this one?

Re: How to create a drug course using dates.

shasank — Thu, 04 Jan 2018 16:02:50 GMT

Hi @ballardw, did the description help. I hope oyu got a chance to look at them.

Re: How to create a drug course using dates.

shasank — Thu, 04 Jan 2018 20:47:52 GMT

@Reeza Can you help me with this question?

Re: How to create a drug course using dates.

shasank — Thu, 04 Jan 2018 20:48:17 GMT

@Tom Can you help me with this question.??

Re: How to create a drug course using dates.

shasank — Fri, 05 Jan 2018 19:33:21 GMT

I am still searching for a solution to this question. Do you have time to have a peak at this question.
Thank you.

Re: How to create a drug course using dates.

shasank — Fri, 05 Jan 2018 19:34:33 GMT

Hi @Astounding,
I would appreciate you help in this problem. Could you please let me know if there is a way to solve this.
Thank you.

Re: How to create a drug course using dates.

shasank — Fri, 05 Jan 2018 19:41:25 GMT

Hello Ballardw,
Did you get a chance to look at the question. If possible I would appreciate the help with solving this problem.
Thanks in advance.

Re: How to create a drug course using dates.

novinosrin — Fri, 05 Jan 2018 19:44:15 GMT

@shasank I am afraid I haven't got a clear understanding of your requirement at all to even get a head start to let my brain and fingers do the rest. Unfortunately, This is after looking into your description multiple times. Personally, I am disappointed too for not even being able to give a go. Well, this is where communication matters most. One of us isn't making it easy to help each other. So sorry!

Re: How to create a drug course using dates.

Astounding — Sat, 06 Jan 2018 03:39:50 GMT

I can give you a little guidance, but I can't solve it. I'm having trouble seeing how the output you post matches your description of what you want to achieve. Regardless of what is needed, however, this looks like a case where the problem would become easier if you were to transpose the data. Picture one observation per ID, with variables StartDate1 through StartDate20 and Drug1 through Drug20. (Of course, 20 is an arbitrary number ... you need to create enough variables to hold the data for the patient with the largest number of dates.)

While I rarely recommend transposing data into that form, this strikes me as a problem that would become easier if you were to do that.

Good luck.