topic Re: How to look up value from the other table? in SAS Programming

How to look up value from the other table?

Witch_Kwang — Tue, 08 Oct 2013 08:09:05 GMT

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

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

AncaTilea — Tue, 08 Oct 2013 11:07:41 GMT

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.

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

PGStats — Wed, 09 Oct 2013 02:10:10 GMT

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;

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

Witch_Kwang — Wed, 09 Oct 2013 04:17:41 GMT

Thx very much Anca Tilea and PGstats!!

I'll try it from ur comment.

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

Mit — Fri, 11 Oct 2013 00:19:28 GMT

There is no 30FEB00

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

Mit — Fri, 11 Oct 2013 00:33:47 GMT

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

29Feb2000