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...
this is 300 gb data.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.