topic Re: calculation based on conversion in SAS Programming

calculation based on conversion

Srigyan — Wed, 12 Jun 2019 08:40:23 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 date9. rate;
format conversion_date date9.;
datalines;
01-Jan-17 110
15-Jul-17 96
06-Mar-18 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 conversion

ballardw — Tue, 11 Jun 2019 14:47:10 GMT

First thing, you need to run your example data sets and look closely at the dates generated, especially for end_date.

Second your rate as shown does not produce the values you indicate as wanted because it is 100 times to large to use as is.

One way:

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
;

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

proc sql;
   create table want as 
   select a.*,( a.price*(b.rate/100)) as Newprice
   from table1 as a, table2 as b 
   where  a.start_date le b.conversion_date le a. end_date
   ;
quit;

Re: calculation based on conversion

Srigyan — Tue, 11 Jun 2019 15:36:40 GMT

Apology for inconvinence.I will take care of this in future.

Though this code is not giving desired result.Date range is not changing.

product	start_date	end_date	Price	Newprice
p1	01-Jan-17	18-Jan-18	15	16.5
p1	01-Jan-17	18-Jan-18	15	14.4
p2	08-Jan-17	31-Dec-17	17	16.32
p1	19-Jan-18	15-Jul-18	30	31.2
p2	01-Jan-18	15-Mar-18	19	19.76

Re: calculation based on conversion

ballardw — Tue, 11 Jun 2019 17:45:37 GMT

@Srigyan wrote:

Apology for inconvinence.I will take care of this in future.

Though this code is not giving desired result.Date range is not changing.

You will have to provide a RULE for how the dates change. It is not obvious from your input.

Re: calculation based on conversion

Srigyan — Wed, 12 Jun 2019 08:40:53 GMT

Post edited with explanation, Please help...