topic Re: How can I match two tables by nearest time? in SAS Programming

How can I match two tables by nearest time?

fri0 — Fri, 04 Oct 2013 20:42:01 GMT

Hi, I have a problem trying to match to two tables by nearest time.

I've attached two samples of my tables. The idea is add the column PRICE from MATRIZELEX to MATRIZCB but with a specific condition.

I don't know if is necessary sort the tables, because the matching should avoid that problem.

Then, the condition is: For each row in MATRIZCB, I have to add the PRICE from MATRIZELEX where PRICE has to be the intermediately preceding by time and NEMONICO=TICKER, FECNEG=FECHA and HORNEG>TIME but TIME has to be the nearest time to HORNEG.

I'm sorry if my english is not good. I'll make an example of what I want:

One row in Table MATRIZCB could be:

ID FECNEG HORNEG NEMONICO PRECIO_ORDEN

1262834 24/09/13 12:32:39p.m. FERREYC1 1.83

Looking the table MATRIZELEX for the same date (MATRIZELEX.DATE) and same Nemonico (MATRIZELEX.TICKER) we have 6 results:

FECHA TIME TICKER PRICE

24/09/13 12:33:17p.m. FERREYC1 1.83

24/09/13 12:32:39p.m. FERREYC1 1.81

24/09/13 10:59:21a.m. FERREYC1 1.83

24/09/13 09:58:11a.m. FERREYC1 1.83

So, I only need the PRICE of the record that is lower and nearest with the record in MATRIZCB. As the record in MATRIZCB has the time 12:32:39pm, the matching register has to be record with time 10:59:21a.m. in MATRIZCB.

I tried to use PROC SQL making a join between the tables and getting the differences between times in order to select the minor value but I got multiple values by each row in MATRIZCB. Could you help me please? There is no difference for me if you use sql o data or whatever. Thanks in advance!

Re: How can I match two tables by nearest time?

Reeza — Fri, 04 Oct 2013 21:16:18 GMT

How big will your datasets be in reality? There are many ways to do this...some more efficient than others.

Re: How can I match two tables by nearest time?

fri0 — Fri, 04 Oct 2013 21:28:07 GMT

About 30,000 rows

Re: How can I match two tables by nearest time?

Reeza — Fri, 04 Oct 2013 22:33:42 GMT

Here's one inefficient way, untested and a skeleton.

Basically, do the join and then use a data step to take only the first record which would be the earliest match? You may need the last instead, I haven't considered the problem thoroughly.

proc sql;

create table want1 as

select a.*, b.price

from table1 a

left join table2 b

NEMONICO=TICKER and FECNEG=FECHA and HORNEG>TIME

order by NEMONICO, FECNEG , HORNEG;

quit;

data want;

set want1;

by NEMONICO FECNEG;

if first.FECNEG;

run;

Re: How can I match two tables by nearest time?

fri0 — Thu, 10 Oct 2013 19:55:10 GMT

Hi! I only had to make a minimum changes and it works. I'd forgotten that you can use > or < while matching. Thank you very much!