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...
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;
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 |
@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.
Post edited with explanation, Please help...
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.