How to look up value from the other table?

Reply
New Contributor
Posts: 3

How to look up value from the other table?

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


Super Contributor
Posts: 543

Re: How to look up value from the other table?

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.

Respected Advisor
Posts: 4,649

Re: How to look up value from the other table?

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

PG
New Contributor
Posts: 3

Re: How to look up value from the other table?

Thx very much Anca Tilea and PGstats!!

I'll try it from ur comment.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: How to look up value from the other table?

There is no 30FEB00

Frequent Contributor
Frequent Contributor
Posts: 83

Re: How to look up value from the other table?

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

Ask a Question
Discussion stats
  • 5 replies
  • 253 views
  • 2 likes
  • 4 in conversation