Calculating Cost with a Changing Rate

[cobba]

I have a table of contracted rates for different companies that change depending on the length of duration and get updated once a year. Then I also have a table of events which consists of the company, duration and Item Code.

data have_rate_table;
input Supplier $ Item $ Sub_Code $ Group $ Duration $ Rate_Start :DATE9. Rate_Finish :DATE9. Rate ;
format Rate_Start DATE9. Rate_Finish DATE9.;
datalines ;
ACME A001 A001_1 1 1-2 01JUL2017 30JUN2018 1000
ACME A001 A001_2 2 3-14 01JUL2017 30JUN2018 500
ACME A001 A001_3 3 15+ 01JUL2017 30JUN2018 200
ACME A001 A001_1 1 1-2 01JUL2018 30JUN2019 1105
ACME A001 A001_2 2 3-14 01JUL2018 30JUN2019 545 
ACME A001 A001_3 3 15+ 01JUL2018 30JUN2019 215
ACME A002 A002_1 1 1-4 01JUL2017 30JUN2018 800
ACME A002 A002_2 2 5+ 01JUL2017 30JUN2018 650
ACME A002 A002_1 1 1-4 01JUL2018 30JUN2019 820
ACME A002 A002_2 2 5+ 01JUL2018 30JUN2019 700
TidyCorp A001 A001_1 1 1-2 01JUL2017 30JUN2018 1500
TidyCorp A001 A001_2 2 3-14 01JUL2017 30JUN2018 1200
TidyCorp A001 A001_3 3 15+ 01JUL2017 30JUN2018 900
TidyCorp A001 A001_1 1 1-2 01JUL2018 30JUN2019 1550
TidyCorp A001 A001_2 2 3-14 01JUL2018 30JUN2019 1230
TidyCorp A001 A001_3 3 15+ 01JUL2018 30JUN2019 910
TidyCorp A002 A002_1 1 1-4 01JUL2017 30JUN2018 600
TidyCorp A002 A002_2 2 5+ 01JUL2017 30JUN2018 550
TidyCorp A002 A002_1 1 1-4 01JUL2018 30JUN2019 660
TidyCorp A002 A002_2 2 5+ 01JUL2018 30JUN2019 575
;
run;

data have_events;
input Event_ID $ Supplier $ Item $ Duration $ Event_Start :DATE9. Event_Finish :DATE9. ;
format Event_Start DATE9. Event_Finish DATE9.;
datalines ;
E01 ACME A001 2 01JUL2017 02JUL2017
E02 ACME A001 4 01JUL2017 04JUL2017
E03 ACME A001 4 30JUN2018 03JUL2018
E04 TidyCorp A001 16 01JUL2018 16JUL2018
E05 TidyCorp A002 5 27JUN2018 01JUL2018
;
run;

What I want is to calculate the total charges for each event based on the step down contract agreement and also the fact that rates change every year.

data want;
input Event_ID $ Supplier $ Item $ Duration $ Event_Start :DATE9. Event_Finish :DATE9. Total_Chgs ;
format Event_Start DATE9. Event_Finish DATE9.;
datalines ;
E01 ACME A001 2 01JUL2017 02JUL2017 2000
E02 ACME A001 4 01JUL2017 04JUL2017 3000
E03 ACME A001 4 30JUN2018 03JUL2018 3195
E04 TidyCorp A001 16 01JUL2018 16JUL2018 19680
E05 TidyCorp A002 5 27JUN2018 01JUL2018 2975
;
run;

How would one go about calculating this?

[japelin]

I have no idea what you want to calculate and how you want to calculate it, but if you can provide specific conditions with variables, I may be able to get a response.