BookmarkSubscribeRSS Feed
cobba
Obsidian | Level 7

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?

 

1 REPLY 1
japelin
Rhodochrosite | Level 12
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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 311 views
  • 0 likes
  • 2 in conversation