BookmarkSubscribeRSS Feed
Srigyan
Quartz | Level 8

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

 

productstart_dateend_datePrice
p101-Jan-1718-Jan-1815
p119-Jan-1815-Jul-1830
p116-Jul-1831-Mar-1924
p208-Jan-1731-Dec-1717
p201-Jan-1815-Mar-1819
p226-Jul-1831-Mar-1918

 

 

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_daterate
01-Jan-17110%
15-Jul-1796%
06-Mar-18104%

 

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

 

productstart_dateend_datePricenew_pricecalculation to understand,it is not required in tables
p101-Jan-1714-Jul-171516.515*110%
p115-Jul-1718-Jan-181514.415*96%
p119-Jan-1805-Mar-183028.830*96%
p106-Mar-1815-Jul-183031.230*104%
p116-Jul-1831-Mar-192424.9630*104%
p208-Jan-1714-Jul-171718.717*110%
p215-Jul-1731-Dec-171716.3217*96%
p201-Jan-1805-Mar-181918.2419*96%
p206-Mar-1815-Mar-181919.7619*104%
p226-Jul-1831-Mar-191818.7218*104%

 

Please help, how can I calculate this...

4 REPLIES 4
ballardw
Super User

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;
Srigyan
Quartz | Level 8

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

 

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

 

productstart_dateend_datePriceNewprice
p101-Jan-1718-Jan-181516.5
p101-Jan-1718-Jan-181514.4
p208-Jan-1731-Dec-171716.32
p119-Jan-1815-Jul-183031.2
p201-Jan-1815-Mar-181919.76
ballardw
Super User

@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.

Srigyan
Quartz | Level 8

Post edited with explanation, Please help...

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1266 views
  • 2 likes
  • 2 in conversation