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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.