How to look up value from the other table?

[Witch_Kwang]

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

[AncaTilea]

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.

[PGStats]

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

[Witch_Kwang]

Thx very much Anca Tilea and PGstats!!

I'll try it from ur comment.

[Mit]

There is no 30FEB00

[Mit]

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