Thanks for your suggestions. I think that will remove clutter from my code. ... View more

Thanks for you reply. I have posted my code and would really appreciate any input from you. ... View more

Thanks @Ksharp and @Kurt_Bremser , here is my amateurish attempt using ideas from both of you so thanks for that. data have; informat index_date drug_start_date bl_start_date drug_end_date first_lookup_date mmddyy10.; format index_date drug_start_date bl_start_date drug_end_date first_lookup_date mmddyy10.; input ID $ index_date days_supply drug_start_date daily_dose bl_start_date drug_end_date; first_lookup_date=bl_start_date-89; label index_date = "Index date" drug_start_date = "Prescription fill date" drug_end_date = "Prescription end date" bl_start_date = "Baseline start date" first_lookup_date = "Index date - 89 days" daily_dose = "Drug daily dose" days_supply = "Days supply of prescription fill"; datalines; A 05/18/2018 7 02/12/2017 5 05/18/2017 02/18/2017 A 05/18/2018 5 03/01/2017 2.5 05/18/2017 03/05/2017 A 05/18/2018 8 04/02/2017 5 05/18/2017 04/09/2017 A 05/18/2018 3 04/20/2017 5 05/18/2017 04/22/2017 A 05/18/2018 10 05/15/2017 5 05/18/2017 05/24/2017 A 05/18/2018 13 06/18/2017 3 05/18/2017 06/30/2017 A 05/18/2018 15 10/02/2017 5 05/18/2017 10/16/2017 A 09/01/2018 13 06/18/2017 3 09/01/2017 06/30/2017 A 09/01/2018 15 10/02/2017 5 09/01/2017 10/16/2017 ; run; I know the maximum number of prescription fills per ID and Index date combination and used that to reshape the data. For this example it is 7 for ID=A and index date=05/18/2018. Here is the code: proc sort data=have; by id index_date drug_start_date; run; %let cum_dose=95; %let max_fils=7; /*maximum no of unique prescription fill for each ID index date combination*/ %let window_width=89;/*gets 90 days counting the day itself*/ data want; set have; by id index_date drug_start_date; array start_date{&max_fils.} 4. _temporary_; retain start_date:; array end_date{&max_fils.} 4. _temporary_; retain end_date:; array dds{&max_fils.} _temporary_; retain dds:; if first.index_date then do; i=1; call missing(of start_date{*}); call missing(of end_date{*}); call missing(of dds{*}); end; start_date(i)=drug_start_date; end_date(i)=drug_end_date; dds(i)=daily_dose; if last.index_date then do;; array date{455} 4. _temporary_;/*365 days of baseline + 89 days window*/ retain date:; do dy=1 to dim(date); date[dy] = bl_start_date+dy-1; end; array cum{455} _temporary_; retain cum:; array ds{&max_fils.} _temporary_; retain ds:; found=0; index=1; cumulative_dose=0; do aa = index to 455 while (date[aa] le index_date+89); dt=date[aa]-89; cum[aa]=0; format dt mmddyy10.; do ab=1 to dim(dds); /* put dt=;*/ /* put end_date1=;*/ /* put start_date1=;*/ /* put date1 mmddyy10.;*/ /* put eos_global=;*/ if (end_date[ab] ge dt) and (start_date[ab] le date[aa]) then do; ds[ab] = min(end_date[ab]-dt+1, end_date[ab]-start_date[ab]+1, date[aa]-start_date[ab]+1, 90); /* put ds[ab];*/ /* put dt=;*/ /* put date[aa] mmddyy10.;*/ /* put start_date[ab]=;*/ /* put end_date[ab]=;*/ /* put aa=;*/ cum[aa] = cum[aa] + ds[ab]*dds[ab]; /* put cum[aa]=;*/ if cum[aa] ge &cum_dose. then do; found=1; found_date=date[aa]; cumulative_dose=cum[aa]; goto exit; end; end; end; end; exit: output; end; i+1; format found_date mmddyy10.; keep ID index_date found cumulative_dose found_date first_look: last_look: bl_start:; run; This works for the example data and also on a subset of real data. My concern is if there are logical errors that I am not able to see. I would appreciate any suggestions to make this more elegant ("less brute force") and efficient. Thank you both for responding. ... View more

Thanks for this. So when you say single record per ID per date, do you mean per index date of per drug start date? Also does the actual data needs to be reshaped to create a single row for each date between the baseline start and index date or can that be avoided? I would prefer to avoid that since I have millions of subjects. Appreciate your response. ... View more

Thanks for this. Is there a way to to avoid creating a "person-day" files as you do in your simulated data "have". Can this work with the example data that I posted? Thanks and appreciate your response. ... View more

Hey everyone, Thanks for all your responses. These were very helpful! I used parts from most of these and came up with following:   data want;     set have;     format start_date end_date start mmddyy10.;     str_end=length(x);     do i=1 to str_end-1;         char=substr(x, i, 1);             if char ne "0" and substr(x, i+1, 1)="0" then                 do j=i+1 to min(i, str_end) while(substr(x, j, 1)="0");                     substr(x, j, 1)=char;                     i=j;             end;     end;     start_ndx=1;     start_date=start+start_ndx-1;     end_ndx = 0;     end_date=start+end_ndx-1;     x1=substr(x,1,1);     do k=2 to str_end;         if substr(x,k,1) ^= x1 then             do;                 end_ndx = k-1;                 end_date=start+end_ndx-1;                 output;                 start_ndx = k;                 start_date=start+start_ndx-1;                 x1 = substr(x,k,1);         end;     end;     end_ndx = length(x);     end_date=start+end_ndx-1;     output;     drop i j k char; run; proc print data=want;var x x1 start start_ndx end_ndx start_date end_date;run     ... View more

Thanks Reeza, I will try to play around with it and see if I can figure it out. Appreciate all the help. ... View more

Hi Suzanne, thanks for your reply. Your code works but the only issue is that in my actual data the variable "X" pertains to daily medication history of subjects over a period of 10 years. Actual string is of length 3652. There are 300,000 subjects so what this does is create an intermediate "person-day" file which has approximately 800 million records. The final data is manageable though. Is there a way to avoid outputting all those intermediate records? Appreciate any help. Cheers ... View more

Thanks for responding. Yes both, value of X and start can be different for different ID. For example ID=2 can have data looking like this: X="AAABB000111AABB" start=03/24/2010; Cheers!! ... View more