topic Re: Join two tables with latest available data in SAS Programming

Join two tables with latest available data

Myurathan — Wed, 08 Jul 2020 13:51:02 GMT

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

Re: Join two tables with latest available data

novinosrin — Wed, 08 Jul 2020 14:04:15 GMT

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.

Re: Join two tables with latest available data

Astounding — Wed, 08 Jul 2020 14:06:51 GMT

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;

Re: Join two tables with latest available data

Myurathan — Wed, 08 Jul 2020 14:32:27 GMT

@novinosrin. Thank you so much for your quick reply.

How would I have to use one more variable (Currency) to join in your code?
EX: Sales table has a currency column and Exchange rate table also have a current column.

Cheers.

Re: Join two tables with latest available data

Myurathan — Wed, 08 Jul 2020 14:52:05 GMT

Thank you so much for your help.

Re: Join two tables with latest available data

novinosrin — Wed, 08 Jul 2020 14:52:25 GMT

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