Dear Experts,
I will have to join two tables. Table 1 contains information about sales in NOK, and Table 2 contains information on Exchange rates. I will have to join the table to get the appropriate exchange rate to the sales table. Exchange rate table does not contain rates for weekends and holidays. So we will have to use the latest available rate for weekends and Holidays. This process going to be performed in very large tables.
Sales table
Date | Value |
01/01/2020 | 652 |
02/01/2020 | 325 |
03/01/2020 | 632 |
04/01/2020 | 256 |
05/01/2020 | 852 |
06/01/2020 | 369 |
07/01/2020 | 999 |
08/01/2020 | 875 |
Exchange rate table
Date | Exchange rate |
01/01/2020 | 0.6589 |
02/01/2020 | 0.6583 |
03/01/2020 | 0.7412 |
04/01/2020 | 0.9875 |
05/01/2020 | 0.9632 |
08/01/2020 | 0.9873 |
09/01/2020 | 0.9345 |
10/01/2020 | 0.6322 |
Desired Results:
Joined table
Date | Value | Exchange rate |
01/01/2020 | 652 | 0.6589 |
02/01/2020 | 325 | 0.6583 |
03/01/2020 | 632 | 0.7412 |
04/01/2020 | 256 | 0.9875 |
05/01/2020 | 852 | 0.9632 |
06/01/2020 | 369 | 0.9632 |
07/01/2020 | 999 | 0.9632 |
08/01/2020 | 875 | 0.9873 |
Thank you in advance for your help.
Regards,
Myu
An easy way assumes both data sets are sorted by DATE:
data want;
set exchange_rate (in=new_rate) sales (in=keepme);
by date;
retain latest_rate;
if new_rate then latest_rate = exchange_rate;
if keepme;
exchange_rate = latest_rate;
drop latest_rate;
run;
HI @Myurathan Please try if you like-
data sales;
input Date :ddmmyy10. Value;
format date ddmmyy10.;
cards;
01/01/2020 652
02/01/2020 325
03/01/2020 632
04/01/2020 256
05/01/2020 852
06/01/2020 369
07/01/2020 999
08/01/2020 875
;
data Exchange_rate;
input Date :ddmmyy10. Exchange_rate;
format date ddmmyy10.;
cards;
01/01/2020 0.6589
02/01/2020 0.6583
03/01/2020 0.7412
04/01/2020 0.9875
05/01/2020 0.9632
08/01/2020 0.9873
09/01/2020 0.9345
10/01/2020 0.6322
;
data want ;
if _n_=1 then do;
if 0 then set sales Exchange_rate;
dcl hash H (dataset:'Exchange_rate') ;
h.definekey ("date") ;
h.definedata ("Exchange_rate") ;
h.definedone () ;
end;
set sales;
_iorc_=h.find();
run;
Since your dataset sales is sorted by "date". It's very straight forward.
Hi @Myurathan , You could add the variables to definedata list from the exchange table
h.definedata ("Exchange_rate") ;
Set sales;
would anyway read all the variables from sales table.
@Astounding 's merge is perhaps much easier to understand and manage. If I am unfamiliar with Hashing, I wouldn't bother though this particular task is very simple albeit can become problematic later when queries become complex. Nonetheless, I will leave that up to you
An easy way assumes both data sets are sorted by DATE:
data want;
set exchange_rate (in=new_rate) sales (in=keepme);
by date;
retain latest_rate;
if new_rate then latest_rate = exchange_rate;
if keepme;
exchange_rate = latest_rate;
drop latest_rate;
run;
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.