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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: