calculation based on different table

Srigyan — Wed, 12 Jun 2019 13:42:53 GMT

I have a table 1. It tells price for a product for a given date range.

product	start_date	end_date	Price
p1	01-Jan-17	18-Jan-18	15
p1	19-Jan-18	15-Jul-18	30
p1	16-Jul-18	31-Mar-19	24
p2	08-Jan-17	31-Dec-17	17
p2	01-Jan-18	15-Mar-18	19
p2	26-Jul-18	31-Mar-19	18

data Table1;
  input product $ start_date :date7. end_date:date7. Price;
 format start_date end_date date9.;
  datalines;p1 01Jan17 18Jan18 15
p1 19Jan18 15Jul18 30
p1 16Jul18 31Mar19 24
p2 08Jan17 31Dec17 17
p2 01Jan18 15Mar18 19
p2 26Jul18 31Mar19 18
;

I have table 2 which actually tell me at which date pound to euro converation happen in system and what is the rate. So in first case from 1-jan-2017 to 14-jul-2017 pound to euro conversion rate is 110%. if Pound price is 100 pound then in Euro it will be 110.

Conversion_date	rate
01-Jan-17	110%
15-Jul-17	96%
06-Mar-18	104%

So in the case 1 case of table one where price for product p1 from 01-Jan-2017 to 18-Jan-2018 is 15Pound and if we want to convert this in euro then as we see conversion rate from 01-Jan-2017 is 110% so in the output new price from 01-Jan-2017 to till the date conversion rate does not change new price is actual price*110% but when conversion rate changed on 15-July-2017 then new price is actual price*96%. similary onwards happen for other records also. New price comes based on actual price* conversion rate at that period of time and conversion start date is conversion_date in table 2.

data table2;
input conversion_date :date7. rate;
format conversion_date date9.;
datalines;
01Jan17 110
15Jul17 96
06Mar18 104
;

I want following output

product	start_date	end_date	Price	new_price	calculation to understand,it is not required in tables
p1	01-Jan-17	14-Jul-17	15	16.5	15*110%
p1	15-Jul-17	18-Jan-18	15	14.4	15*96%
p1	19-Jan-18	05-Mar-18	30	28.8	30*96%
p1	06-Mar-18	15-Jul-18	30	31.2	30*104%
p1	16-Jul-18	31-Mar-19	24	24.96	30*104%
p2	08-Jan-17	14-Jul-17	17	18.7	17*110%
p2	15-Jul-17	31-Dec-17	17	16.32	17*96%
p2	01-Jan-18	05-Mar-18	19	18.24	19*96%
p2	06-Mar-18	15-Mar-18	19	19.76	19*104%
p2	26-Jul-18	31-Mar-19	18	18.72	18*104%

Please help, how can I calculate this...

Re: calculation based on different table

Reeza — Wed, 12 Jun 2019 15:32:26 GMT

How big are your data sets?
You can use complicated join techniques but an easy way is to convert all records to daily records and to then split based on that.

Re: calculation based on different table

Srigyan — Mon, 17 Jun 2019 12:28:02 GMT

this is 300 gb data.

topic Re: calculation based on different table in SAS Programming

calculation based on different table

Re: calculation based on different table

Re: calculation based on different table