Hello!!
I have data in 2 table and want to join the data together in one to many relationship like "if...match"function in excel.
My data is seem to be;
Table 1
Stock Date Price
A 29Feb00 10
A 28Feb01 13
A 14Aug01 14
B 13Nov01 20
B 28Feb02 21
C 14May02 20
Table 2
Stock Date Time
A 29Feb00 8.00
A 29Feb00 8.05
A 29Feb00 8.10
A 30Feb00 8.00
B 28Feb02 8.00
B 28Feb02 8.05
I want to match price value from table 1 into additional column in table 2 by using both stock and date as key variable.
And results will be;
Stock Date Time Price
A 29Feb00 8.00 10
A 29Feb00 8.05 10
A 01Mar00 8.00 -
B 28Feb02 8.00 21
B 28Feb02 8.05 21
Hi.
I think the third row in your Results table should be
Stock Date Time Price
A 29Feb00 8.00 10
A 29Feb00 8.05 10
A 29Feb00 8.10 10
A 30Feb00 8.00 -
B 28Feb02 8.00 21
B 28Feb02 8.05 21
But, I think a simple left join (table_2 left join table_1) would get you the results you want?
proc sql;
create table results as
select a.stock,a.date,a.time, b.price
from table_2 as a left join table_1 as b
on a.stock = b.stock & a.date = b.date;
quit;
Good luck!
Anca.
Or... even simpler:
proc sql;
create table results as
select a.*, b.price
from table_2 as a natural left join table_1 as b;
quit;
PG
Thx very much Anca Tilea and PGstats!!
I'll try it from ur comment.
There is no 30FEB00
Using data step:
proc sort data=table1;
by stock date;
run;
proc sort data=table2;
by stock date;
run;
data want;
merge table2(in=a) table1(in=b);
by stock date;
if a;
run ;
stock | date | time | price |
A | 29Feb2000 | 8:00 | 10 |
A | 29Feb2000 | 8:05 | 10 |
A | 29Feb2000 | 8:10 | 10 |
A | 29Feb2000 | 8:00 | 10 |
A | 01Mar2000 | 8:00 | |
B | 28Feb2002 | 8:00 | 21 |
B | 28Feb2002 | 8:05 | 21 |
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.