I am working with data on prescription drugs (drug-level data) with start and end dates for prescriptions for large number of individuals. For each individual in drug, say, XYZ, I want to identify the first date when they had filled prescriptions for at least 40 days within 80 days time-frame without a gap of more than 20 days (allowable gap of 20 days). Below is a part of the data. data drug_dates; input id $ start_dt end_dt; informat start_dt yymmdd10. end_dt yymmdd10.; format start_dt yymmdd10. end_dt yymmdd10.; datalines; A 2017/01/01 2017/01/10 A 2017/01/16 2017/02/04 A 2017/03/02 2017/03/11 A 2017/04/01 2017/04/15 A 2017/05/06 2017/05/15 A 2017/05/26 2017/06/14 ; run; Below is the logic I tried to use. Basically, I expanded all the prescriptions vertically, with each row for each day, and tried to identify the gaps. But I still cannot identify the first date which indicates that they have reached 40 days of use in 80 days without gap of more than 20 days. I am not sure how to do an iterative sum across rows. Thank you! data drug_dates1; set drug_dates; do each_day=start_dt to end_dt; output; end; format each_day yymmdd10.; run; proc sql; create table all_dates as select distinct id, min(start_dt) as min_start format yymmdd10., max(end_dt) as max_end format yymmdd10. from drug_dates group by id; quit; data all_dates1; set all_dates; do each_day=min_start to max_end; output; end; format each_day yymmdd10.; run; proc sql; create table combined as select * from all_dates1 a left join drug_dates1 b on a.id=b.id and a.each_day=b.each_day order by id, each_day; quit;   ... View more

I have a dataset that looks like all_dates dataset below:   data all_dates; input id: $1. start_dt: mmddyy10. drugA_dt: mmddyy10. drugB_dt: mmddyy10. drugC_dt: mmddyy10. drugD_dt: mmddyy10. drugE_dt: mmddyy10. stop_dt: mmddyy10.; format start_dt drugA_dt drugB_dt drugC_dt drugD_dt drugE_dt stop_dt mmddyy10.; datalines; A 01/01/2017 01/25/2017 02/18/2017 03/28/2017 05/10/2017 07/05/2017 12/31/2017 ; run; I want to create multiple rows for the individuals based on the minimum of all dates except for start_dt. In the given example, the start_dt of second row should be the lowest of all other dates and stop_dt should be the second lowest. My final dataset should look something like this: data want; input id: $1. start_dt: mmddyy10. drugA_dt: mmddyy10. drugB_dt: mmddyy10. drugC_dt: mmddyy10. drugD_dt: mmddyy10. drugE_dt: mmddyy10. stop_dt: mmddyy10.; format start_dt drugA_dt drugB_dt drugC_dt drugD_dt drugE_dt stop_dt mmddyy10.; datalines; A 01/01/2017 01/25/2017 02/18/2017 03/28/2017 05/10/2017 07/05/2017 01/25/2017 A 01/25/2017 01/25/2017 02/18/2017 03/28/2017 05/10/2017 07/05/2017 02/18/2017 A 02/18/2017 01/25/2017 02/18/2017 03/28/2017 05/10/2017 07/05/2017 03/28/2017 A 03/28/2017 01/25/2017 02/18/2017 03/28/2017 05/10/2017 07/05/2017 05/10/2017 A 05/10/2017 01/25/2017 02/18/2017 03/28/2017 05/10/2017 07/05/2017 07/05/2017 A 07/05/2017 01/25/2017 02/18/2017 03/28/2017 05/10/2017 07/05/2017 12/31/2017 ; run; Notice how the start_dt and stop_dt move. I have been struggling with this and cannot figure out the solution. Thank you! ... View more

I am in the process of performing survival analysis. My main exposure variable and 8 other variables (covariates) are time-varying. I have some experience on time-varying analysis, but each time I have had only one time-varying variable. In such cases, I would have a separate row/observation (for every individual) for every change in the value of my time-varying variable. However, with multiple time-varying variables, I am not sure how I should arrange rows. I tried to look online for this; all I found was a passing comment that the rows should be arranged such that all the variables are constant within that row. I could not find good source for this. Does it mean that I need to create separate rows if any of my time-varying (9 in total) variable changes? This seems like a lot of work, so any suggestion, link to references would be of much help? Thank you! ... View more

I have a dataset that looks like the following.  id  from_date to_date drug 111 2017-03-01 2017-03-30 A 111 2017-03-01 2017-03-30 B 111 2017-03-15 2017-04-15 C_D 112 2017-08-10 2017-09-10 A 112 2017-08-10 2017-09-10 A_B   I want to count the number of days for which one or more drugs are used. I use the following codes: data expand; set have; do day=from_date to to_date; output; end; run; proc sql; create table counted as select *, count(distinct drug) as num_drug from expand group by id, day; quit; The problem with this code is that I cannot know which combination people are in and also sometimes undercounts and overcounts the number of drugs used. In the above example, I would want the combination of A, B and C_D to be counted as four (C_D is a combination product which contains two drugs), but I get the count as three. To account for this I created another dataset using the following code:   proc sql; create table new_counted as select *, case when drug in ("A_B", "C_D") then num_drug+1 else num_drug end as new_count from counted group by id, day,new_count desc; quit; This solves one problem but creates another. In the above example for id 112, it would give me a count of 3 which would be wrong (A and A_B should equal to two drug use). I am running in circles here. How could I get both number of days for total drugs use along with the type of combination? Thank you! ... View more