topic Re: creating variable based on observation from multiple variables within the time frame in SAS Programming

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

yongheelee1212 — Fri, 23 Aug 2019 14:48:03 GMT

Dear SAS users,

I am new to longitudinal data analysis and especially when it comes to manipulating date related issue.

I have fairly complicated issue, and any tip along the way is much appreciated.

I have a data that contains ID, date, and therapy, and my ultimate goal is to create course of treatment.

Course of treatment inclement with certain condition:

1. if drug that is not part of previous COT is observed and

2. the drug is observed within 30 days

So for instance, drug2 is observed at 10/16/2012 and observed twice within 30 days. So, this is the new course of treatment.

Then all_drug within that course_of_treatment would be all drugs observed during 30 days from first day of course_of_treatment,

which would be drug 2 and 3.

Course_of_treatment continues until drug4 is observed.

Any comment would be great and thank you!

The output I want looks as below

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

Reeza — Fri, 23 Aug 2019 15:12:40 GMT

Which variables there do you start with and which do you need to calculate?

Can you please provide sample data as text, especially if want example code.

You would use a SQL merge here because you can merge on a range then to find occurrences within 30 days. You would first merge the dataset with itself to determine that. Then once you find the counts, you could create episodes/groups. Then use CATX() to combine them to find the course of treatment over time. You'll also want to think about how exactly you want that output data set to look like.

@yongheelee1212 wrote:

Dear SAS users,

I am new to longitudinal data analysis and especially when it comes to manipulating date related issue.

I have fairly complicated issue, and any tip along the way is much appreciated.

I have a data that contains ID, date, and therapy, and my ultimate goal is to create course of treatment.

Course of treatment inclement with certain condition:

1. if drug that is not part of previous COT is observed and

2. the drug is observed within 30 days

So for instance, drug2 is observed at 10/16/2012 and observed twice within 30 days. So, this is the new course of treatment.

Then all_drug within that course_of_treatment would be all drugs observed during 30 days from first day of course_of_treatment,

which would be drug 2 and 3.

Course_of_treatment continues until drug4 is observed.

Any comment would be great and thank you!

The output I want looks as below

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

yongheelee1212 — Fri, 23 Aug 2019 15:41:59 GMT

Yes, thank you.

Attached is the date I have.

Ultimately, I would like to create variable: Course_of_treatment and if possible, drugs_within_COT.

ID Date Therapy
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

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

Reeza — Fri, 23 Aug 2019 15:59:23 GMT

Why isn't drug1 the first COT for ID1?

Unfortunately I likely don't have enough time today to wade into a problem this complex but this should get you (or anyone else started at least 🙂

data have;
informat ID $1. date mmddyy10. therapy $8.;
format date date9.;
input ID $ Date Therapy $;
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
2 10/1/2011   drug1
2 10/16/2012 drug1
2 10/30/2012 drug2
2 10/30/2012 drug2
2 11/13/2012 drug3
2 11/27/2012 drug2
2 12/11/2012 drug2
2 12/11/2012 drug3
2 12/18/2012 drug2
2 12/24/2012 drug3
3 12/31/2012 drug2
3 1/8/2013 drug3
3 1/15/2013 drug2
3 1/22/2013 drug2
3 1/29/2013 drug3
3 2/5/2013 drug3
3 2/12/2013 drug1
3 2/19/2013 drug4
3 2/26/2013 drug4
3 3/5/2013 drug4
;;;;

proc sql;
create table counts as
select *, 
	(select count(*) as num 
	from have as h1_x 
	where h1_x.id=h1.id 
	and h1.therapy = h1_x.therapy 
	and h1_x.date between h1.date and (h1.date+30))
		as count
from have as h1
order by 1, 3, 2;
quit;

@yongheelee1212 wrote:

Yes, thank you.

Attached is the date I have.

Ultimately, I would like to create variable: Course_of_treatment and if possible, drugs_within_COT.

ID Date Therapy
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

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

yongheelee1212 — Fri, 23 Aug 2019 16:05:07 GMT

thank you so much!

drug1, the first observation is not COT1 because one of the criteria is that it should've been given at least twice within 30 days.

First one was given at 10/1/2011 and the next one is at 10/16/2012. So it doesn't meet the criteria.

much appreciated for your help with start of the approach!

time to do some research and dig into the problem!

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

Reeza — Fri, 23 Aug 2019 16:08:43 GMT

I know that this question has been asked and answered on here many times. If you do some searching you should find better answers that are much closer to what you need than this.

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

novinosrin — Fri, 23 Aug 2019 19:16:58 GMT

Hello @yongheelee1212 Looks like you are new to SAS communities. So hearty welcome!

One request is, you could explain your requirement in a more comprehensible detail for anyone to understand and attempt.

I have a data that contains ID, date, and therapy,

Requirement goal is to create course of treatment. so that is COT variable, so what's in with count_30days and part_of_previous_COT variable???

Objective : To increment Course of treatment values with certain condition

1. if drug that is not part of previous COT/*what is this first? How is this computed or derived?*/ is observed and

2. the drug is observed within 30 days /*within 30 days of what????from when/where? */

Please explain the logic relating to sample OUTPUT(variables) how is it derived? The flow should be convenient to grasp even for some dumb folks like me. Thank you!

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

yongheelee1212 — Fri, 23 Aug 2019 21:18:14 GMT

Dear novinosrin

My apology if my requirements were ambiguous.

TO answer your question,

Objective : To increment Course of treatment values with certain condition

1. if drug that is not part of previous COT/*what is this first? How is this computed or derived?*/ is observed and

2. the drug is observed within 30 days /*within 30 days of what????from when/where? */

Course of Treatment is defined if the therapy given is not part of the previous course of treatment and if the drug is given at least twice in 30 days. So for instance, at the beginning, course of treatment is none. so first requirement is already satisfied but the drug has to be given at least twice in 30 days. Drug 1 from first and second observation would not be first course of treatment because it does not meet the second requirement.

So as you can see from the top picture, drug 2(10/30/2012) would be beginning of course of treatment, hence cot1. The drugs observed from 10/30/2012 to 11/30/2012 is drug 2 and drug 3, hence drugs associated with cot1 would be drug2 and drug3).

cot1 continues until new drug is observed consecutively in 30 days, which is drug4 on 3/5/2013. hence, cot2 starts on 3/5/2013.

I hope that explains better, let me know if you have more question.

So far, I tried to do this,

1. create Count_30_days (which shows number of drug given within 30 day of the date that drug is given.

So my intended strategy is to do:

*** identify whether the therapy given on specific date is part of COT***

part_of_therapy = yes

if (therapy in(therapy) BETWEEN first.date of COT) and first.date of COT+30ays

*** then based on that do something like ***

course of treatment +1

course_30_days>=2 (happens at least twice in 30 days) and part_of_therapy=yes (if the therapy is part of the treatment).

To do this, I think I need to know how to identify observations in a variable within specific date range, but that's something I just couldn't figure out no matter what.

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

yongheelee1212 — Fri, 23 Aug 2019 21:21:08 GMT

Thank you for the response!

I actually tried spending many hours looking up SAS communities and other help websites. I was able to find similar looking questions but not as same as mine.

So my intended strategy is to do:

*** identify whether the therapy given on specific date is part of COT***

part_of_therapy = yes

if (therapy in(therapy) BETWEEN first.date of COT) and first.date of COT+30ays

*** then based on that do something like ***

course of treatment +1

course_30_days>=2 (happens at least twice in 30 days) and part_of_therapy=yes (if the therapy is part of the treatment).

To do this, I think I need to know how to identify observations in a variable within specific date range and perhaps create variable based on observations from other variable in certain timeframe, but that's something I just couldn't figure out no matter what.

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

novinosrin — Fri, 23 Aug 2019 21:21:16 GMT

Hi @yongheelee1212 Nothing to apologize. You didn't know. Sorry, I'm about to run to catch my Bus to get home. I'll see if I can get online from home and attempt from home.

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

yongheelee1212 — Fri, 23 Aug 2019 21:23:52 GMT

Thank you!

If you have any other suggestions on how I should approach this, I am open to all ideas!

have a wonderful Friday!

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

Reeza — Fri, 23 Aug 2019 21:38:52 GMT

@yongheelee1212 wrote:

To do this, I think I need to know how to identify observations in a variable within specific date range and perhaps create variable based on observations from other variable in certain timeframe, but that's something I just couldn't figure out no matter what.

The in line sql above shows how to look up data based on a range.

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

KachiM — Sat, 24 Aug 2019 09:20:04 GMT

Hi @yongheelee1212

Something I am missing in ....

cot1 continues until new drug is observed consecutively in 30 days, which is drug4 on 3/5/2013. hence, cot2 starts on 3/5/2013.

COT1 starts with drug1 on 10/16/2012 continues with drug2 and drug3 with 30-day gaps through 2/5/2013. On 2/12/2013 drug1 comes back. Drug4 joins with drug1 on 2/19/2013.

Questions are:

[1] Hence, COT1 joining with drug4 is deemed to be a new sequence. Is this right?

[2] why do you say Cot2 starts on 3/5/2013 when Drug4 was started earlier on 2/19/2013?

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

novinosrin — Sat, 24 Aug 2019 20:39:10 GMT

Hello @yongheelee1212

Let me try to pen my understanding in points and see the flow.

My observation

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?

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

3. Drug1 of obs 1 will of course not be part of previous treatment because that's the start.

4. Look up(Look ahead in 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.

5. 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 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.

6. Repeat the same process for every record until last.patient_id i.e last record of the BY GROUP patient id.

7. Follow same suit for all Patients or Patient ID aka exclusive BY GROUP.

Does this make sense closer to your need?.

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

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
;
/*To Test the look up by remvoe the duplicate dates for the same therapy to 
know if our expected count is right. of course later in our solution code, 
we can include the full data.
Example 1 10/30/2012 drug2
1 10/30/2012 drug2  occurs twice in have 
*/

proc sort data=have out=_have nodupkey;
by id therapy date;
run;

/*please notice the below in the code*/
/*on a.id =b.id and a.date<=b.date<=intnx('days',a.date,30)*/
/*a.therapy=b.therapy as c_to_count.This is boolean expression resulting in 1,0's*/
/*If you sum(c_to_count), you would get count */

proc sql;
create table Count_30 as
select a.*,a.date as start_date format=mmddyy10.,intnx('days',a.date,30) as end_date format=mmddyy10., a.therapy=b.therapy as c_to_count
from _have a left join _have b
on a.id =b.id and a.date<=b.date<=intnx('days',a.date,30)
group by a.id,a.date,a.Therapy
order by a.id,a.date,b.date;
quit;

Now, run the above, look at the results. If this study gets us the right direction, we can apply whether the found Drugs within the 30 period was part of the existing course_of_treatment. That will involve a a look up in all_drug_30 and club these if true or increment a new one.

All these can be done neatly using Hash for a final complete solution. But the above initial study is just to see if our understanding of the logic is in the same page if that helps. In essence, I get the feeling that while I am not understanding well or taking time, the coding part is merely gonna take 5 or 10 mins.

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

yongheelee1212 — Mon, 26 Aug 2019 02:18:21 GMT

Dear datasp.

1, you are correct.

2. my apology. you are right, cot2 should start on 2/19/2013,, there was an mismatch in original picture and data in text I presented

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

yongheelee1212 — Mon, 26 Aug 2019 03:01:40 GMT

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

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

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

Yes!

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!

Drug1 of obs 1 will of course not be part of previous treatment because that's the start.
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.

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).

Repeat the same process for every record until last.patient_id i.e last record of the BY GROUP patient id.
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 -->

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

novinosrin — Mon, 26 Aug 2019 17:19:21 GMT

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;

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

yongheelee1212 — Thu, 29 Aug 2019 18:15:21 GMT

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-and-flag-it-to/td-p/425362

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

novinosrin — Thu, 29 Aug 2019 19:30:09 GMT

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

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

Reeza — Thu, 29 Aug 2019 20:43:20 GMT

@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-and-flag-it-to/td-p/425362

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