creating variable based on observation from multiple variables within the time frame

[yongheelee1212]

Thank you for taking your step with me!! Very much appreciated for your time

Let's start by  reading  record(obs)  1.

1. First off, Date column has the dates when a drug or sometimes more than a 1 drug administered?

Yes!

1. Point 1 would ideally make sense for the Course_of_treatment sequenceto start right from record 1 or in other words from the date of the start of treatment, which you have blank.

Yes!

1. Drug1 of obs 1 will of course not be part of previous treatment because that's the start.
2. Look up(Look aheadin this case) 30 days from 10/1/2011(inclusive) to (10/1/2011 +30 days) i.e date to  intnx('days',date,30)   precisely. This should be done for each record within a patient ID i.e BY GROUP

a.id =b.id and a.date<=b.date<=intnx('days',a.date,30) in a would be SQL syntax and get all the drugs.

1. Since <= makes it inclusive, if  a.date<=b.date<=intnx('days',a.date,30) is found true 2 times(twice) or more then conditional check 1 is satisfied. Then if anyof the Drug found true happens to be part of the previous treatment, club all of the found uniquedrugs to the previous treatment to which it is found.

Yes, if the drug is found true 2 times or more then conditional check 1 is satisfied (drug is given twice in 30 days, which marks the start of the COT).

I am not quite sure what this part means “Then if any of the Drug found true happens to be part of the previous treatment, club all of the found unique drugs to the previous treatment to which it is found”.

If any of the drug found within 30 days of start of treatment, that drug is part of COT.

So, for instance, based on your data set,

From 10/30/2012 to 11/29/2012, drug2 is found twice.

Thus, 10/30/2012 is the start of COT1.

Any drug found from 10/30/2012 – 11/29/2012 is COT1 (drug2, drug3).

Then,

02/19/2013, drug 4 is given.

Drug 4 is not part of COT1 (drug2, drug3).

From 02/19/2013 – 03/18/2013, drug 4 is given at least twice.

Thus, 02/19/2013 is start of COT2.

Any drug found from 02/19/2013 – 03/18/2013 is COT2 (drug4).

1. Repeat the same process for every record until last.patient_id i.e last record of the BY GROUP patient id. 
2. Follow same suit for all Patients or Patient ID aka exclusive BY GROUP.

Does this make sense closer to your  need?.

I ran the code, and yes it is to the right direction!! sum(c_to_count) does provide count of drug given twice from the date! thank you. 

Thank you so much. I replied with bold letter to your steps. I hope this makes more sense.

Part Logic for count_30 days of a drug for each observation -->

Lets do some initial findings using SQL applying the LOOK UP to get the dates and see if the Drug was found twice or more. This will help us understand whether our thought process in applying the logic for LOOK UP is right or not

[novinosrin]

Hello @yongheelee1212   Little tired after not so good sleep over the weekend,  and so some attention to detail may miss accuracy, but test and let me know the discrepancies, I'll fix it. Do feel free, some warm water and tea should get me charged up again. 

data have;
input ID Date :mmddyy10. Therapy $;
format date mmddyy10.;
cards;
1 10/1/2011   drug1
1 10/16/2012 drug1
1 10/30/2012 drug2
1 10/30/2012 drug2
1 11/13/2012 drug3
1 11/27/2012 drug2
1 12/11/2012 drug2
1 12/11/2012 drug3
1 12/18/2012 drug2
1 12/24/2012 drug3
1 12/31/2012 drug2
1 1/8/2013 drug3
1 1/15/2013 drug2
1 1/22/2013 drug2
1 1/29/2013 drug3
1 2/5/2013 drug3
1 2/12/2013 drug1
1 2/19/2013 drug4
1 2/26/2013 drug4
1 3/5/2013 drug4
;


data want ;
if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ('_Therapy',"_Date") ;
   h.definedata ('_Therapy',"_Date") ;
   h.definedone () ;
   dcl hash H1 () ;
   h1.definekey  ('_Therapy') ;
   h1.definedata ('course_of_treatment') ;
   h1.definedone () ;
   dcl hiter hi('h');
end;
do _n_=1 by 1 until(last.id);
 set have;
 by id date;
 _therapy=therapy;
 _date=date;
/*Load therapy and date as keys to the look up table for a given ID*/
 rc=h.add();
end;
/*Intitialize course_of_treatment with a value of 1 i.e
start of treatment*/
course_of_treatment=1;
do _n_=1 to _n_;
 set have;
 call missing(count,_f);
/*Look up to get the count*/
 do while(hi.next()=0);
  if date<=_date<=intnx('days',date,30) then 
  count=sum(count,_therapy=therapy);
/*Check if it is part of previous treatment and flag it*/
  if h1.find()=0 then _f=1;
 end;
/*If it satisfies both conditions, incrememnt course_of_treatment by 1 */
 if count>=2 and _f=1 then course_of_treatment+1;
 do while(hi.next()=0);
/*Load all the therapies associated with course_of_treatment
 to facilitate check for next iteration*/
  rc1=h1.add();
 end;
 output;
end;
/*Save memory space by clearing the contents of Hash*/
h.clear(); h1.clear();
drop rc: _:;
run;

[yongheelee1212]

Dear @novinosrin thank you so much for the help. 

With your help and previous post (which is also assisted by you), I was able to find the answer

https://communities.sas.com/t5/General-SAS-Programming/How-to-identify-a-change-across-observations-...

[novinosrin]

Okay cool. We are glad. 🙂 Have fun! and take care!

[Reeza]

---

@yongheelee1212 wrote:

Dear @novinosrin thank you so much for the help. 

With your help and previous post (which is also assisted by you), I was able to find the answer

https://communities.sas.com/t5/General-SAS-Programming/How-to-identify-a-change-across-observations-...

---

Please mark the question as solved and note the appropriate solution.

[novinosrin]

Yeah and that will have to be your own post i.e  the following one .

---

@yongheelee1212 wrote:

Dear @novinosrin thank you so much for the help. 

With your help and previous post (which is also assisted by you), I was able to find the answer

https://communities.sas.com/t5/General-SAS-Programming/How-to-identify-a-change-across-observations-...

---

IMHO, you must be an expert researcher 🙂