How to identify observed claims within a period of time?

[jsyang56]

Hello experts, could you guys help me out and show me the most efficient way to solve the problem I have?

How can I identify the first observed claims for drugs of interest within the first 28 days after the first claim to identify the first line of therapy associated with each ID? 

Also, if the treatments change after the first 28 days, how can I capture the change and record for the next LOT? 

Here is an example of the dataset I currently have,

ID	Claim date	Drug Name - hospital administered	Drug - pharmacy prescription	Drug Class	Days Supply
1111	06/20/2016	J	.	A	.
1111	06/27/2016	J	.	A	.
1111	07/05/2016	J	.	A	.
1111	07/14/2016	J	.	A	.
1111	07/19/2016	J	.	A	.
1111	08/03/2016		K	B	28
1111	08/03/2016		L	C	28
1111	08/10/2016		d	d	84
2222	05/01/2016		K	B	28
2222	05/01/2016	J		A
2222	05/01/2016		d	d	28
2222	05/29/2016		K	B	28
2222	05/29/2016	J		A
2222	05/29/2016		d	d	28

From the table above, I would like to change to look like this, 

ID	LOT1	LOT_start date	LOT_end date	LOT2	LOT2_start date	LOT2_ end date
1111	A	06/20/2016	08/03/2016	B + C + d	08/03/2016	08/31/2016
2222	B + A + d	05/01/2016

Please let me know if this is possible! 

[HB]

You'll need to explain how 1111/ d is part of lot 2 given that its claim date is different from b and c.
What defines a lot? What define a treatment change?

You'll need to explain the derivation of the lot2 end date.
You'll need t explain why 2222 isn't finished