How to create a drug course using dates.

[shasank]

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.

[ballardw]

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.

[shasank]

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

[shasank]

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

[novinosrin]

@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

[shasank]

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.

[novinosrin]

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?

[shasank]

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.

[novinosrin]

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!

[shasank]

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

[novinosrin]

@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!

[shasank]

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

[shasank]

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.

[shasank]

@Astounding Can you help me with this one?

[shasank]

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.