Hi @Pabster    Are you sure that DLDTE is a year and not a date value?   ... View more

Sorry   It is here ... View more

Hi @Srigyan    Here is a bit of code to transform the override dataset.   data work.b; informat Override_date anydtdte9.; input Product $ Override_date price; datalines; A 10/01/2018 11 A 11/01/2018 12 A 13/01/2018 9 A 14/01/2018 5 ; run; data b (rename=(price=New_price)); format Override_date End_date ddmmyyd10.; set b; by Product; p = _N_+1; if not last.Product then do; set b(drop=price rename=(Override_date=end_date)) point=p; end_date = end_date - 1; end; else end_date = '31dec9999'd; run; ... View more

Hi @Srigyan    I have a macro to handle problems like this. It is an interval merger that takes several intervals for the same ID in both datasets and accepts missing periods between intervals. It works with subgroups too. It is made to handle large data sets, so it is quite effecient.    It requires intervals in both data sets, and besides interval- and ID variables it cannot accept variables withe same name in both data sets, so something must be done to add end dates to the override table and chane the price variable name before calling the macro. And the documentation is written in danish. It is big, so I have attached it as a file. You are free to use it, and I will be happy to help with any questions.   It is used like this:     data work.a; informat St_date End_date anydtdte9.; input Product $ St_date End_date Price; datalines; A 17/12/2017 09/01/2018 8 A 10/01/2018 15/01/2018 10 ; run; data work.b; informat Override_date End_date anydtdte9.; input Product $ Override_date End_date new_price; datalines; A 10/01/2018 10/01/2018 11 A 11/01/2018 12/01/2018 12 A 13/01/2018 13/01/2018 9 A 14/01/2018 31/12/9999 5 ; run; %FletrensInterval(work.a, work.b, work.want, unita=Product, datefirsta=St_date, datelasta=End_date, unitb=Product, datefirstb=Override_date, datelastb=End_date, join=left);               ... View more

Kudos! - Your solution makes me feel like this: https://www.shutterstock.com/da/image-vector/simple-cartoon-businessman-knocking-his-head-443053690 ... View more

Hi @Patrick    Very elegant indeed - I didn't think of that. I had a hash lookup in mind, but (I am ashamed to admit) most of my toolbox is pre-V9, and after 15 years with V9 I am still not really familiar with hash objects, so it would take me too long to figure out.   But I could not resist running a test to compare the two solutions. I used 24 months in A and 5.000.000 observations in B with random intervals. SQL is the winner:   236 data A (drop=i s); 237 format begin end mmddyy10.; 238 s = '01dec2017'd; 239 do i = 1 to 24; 240 begin = intnx('month',s,i); 241 end = intnx('month',begin,1)-1; 242 month = put(begin,monyy.); 243 output; 244 end; 245 run; NOTE: The data set WORK.A has 24 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 246 247 data B; 248 format recur_start recur_end yymmdd10.; 249 do custid = 1 to 5000000; 250 recur_start = (ranuni(1) * 730) + 21000; 251 recur_end = min(recur_start + (ranuni(3) * 730),22100); 252 recur_amt = int(ranuni(5)*1000); 253 output; 254 end; 255 run; NOTE: The data set WORK.B has 5000000 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.45 seconds cpu time 0.45 seconds 256 257 data want(keep=month begin end recur_amt); 258 set b; 259 _last=0; 260 do _i=1 to _nobs; 261 set a point=_i nobs=_nobs; 262 if begin<=recur_end and end>=recur_start then output want; 263 end; 264 run; NOTE: There were 5000000 observations read from the data set WORK.B. NOTE: The data set WORK.WANT has 51607422 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 17.74 seconds cpu time 16.90 seconds 265 266 proc sql; 267 create table want as 268 select a.month, b.custid, b.recur_start, b.recur_end, b.recur_amt 269 from A, B 270 where 271 a.begin <= b.recur_end 272 and a.end >= b.recur_start; NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. NOTE: Table WORK.WANT created, with 51607422 rows and 5 columns. 273 quit; NOTE: PROCEDURE SQL used (Total process time): real time 9.92 seconds cpu time 8.40 seconds I ran this on a Lenovo PC with SSD disk. I also tried it on a Linux Grid running a heavy batch load at the moment, and got almost the same figures:   21 22 data want(keep=month begin end recur_amt); 23 set b; 24 _last=0; 25 do _i=1 to _nobs; 26 set a point=_i nobs=_nobs; 27 if begin<=recur_end and end>=recur_start then output want; 28 end; 29 run; NOTE: There were 5000000 observations read from the data set WORK.B. NOTE: The data set WORK.WANT has 51607422 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 14.18 seconds cpu time 13.16 seconds 30 31 proc sql; 32 create table want as 33 select a.month, b.custid, b.recur_start, b.recur_end, b.recur_amt 34 from A, B 35 where 36 a.begin <= b.recur_end 37 and a.end >= b.recur_start; NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. NOTE: Table WORK.WANT created, with 51607422 rows and 5 columns. 38 quit; NOTE: PROCEDURE SQL used (Total process time): real time 8.39 seconds cpu time 8.39 seconds ... View more

Hi @redfishJAX    One way is an SQL join. The might be more effecient solutions, but I think this would run pretty fast too, because the A data set is small, and it is simple.     data A; informat begin end mmddyy10.; format begin end mmddyy10.; input month $ begin end; datalines; Jan19 1/1/2019 1/31/2019 Feb19 2/1/2019 2/28/2019 ; run; data B; informat recur_start recur_end mmddyy10.; format recur_start recur_end mmddyy10.; input custid recur_start recur_end recur_amt; datalines; 1 10/15/2014 2/16/2019 150 2 2/18/2018 1/31/2019 150 3 12/15/2012 3/31/2021 100 ; run; proc sql; create table C as select a.month, b.custid, b.recur_start, b.recur_end, b.recur_amt from A, B where a.begin <= b.recur_end and a.end >= b.recur_start; quit;       ... View more