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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.