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:33:17p.m. FERREYC1 1.83
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!
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
on
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;
How big will your datasets be in reality? There are many ways to do this...some more efficient than others.
About 30,000 rows
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
on
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;
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.