proc sql merge -error

[LanMin]

Dear all,

I use the code below to do a left out join and keep getting error message.

PROC SQL;

CREATE TABLE LEFT_OUTER_JOIN AS

SELECT *

FROM use(KEEP=IndIO ComIO ProducerPr PurchaserPr)

LEFT JOIN

Make (KEEP=IndOut)

ON use.IndIO = make.IndIO;

run;

ERROR: Column IndIO could not be found in the table/view identified with the correlation name

       MAKE.

ERROR: Column IndIO could not be found in the table/view identified with the correlation name

       MAKE

My use and make data are sas dataset. IndIo is Best $6, and Best $7. I wonder if this information difference is causng the error.

thanks and happy holidays,

Lan

[art297]

With make you are only keeping IndOut, but then trying to use make.IndIO

[LanMin]

Thanks Arthur!

I made the change

PROC SQL;

CREATE TABLE LEFT_OUTER_JOIN AS

SELECT *

FROM use(KEEP=IndIO ComIO ProducerPr PurchaserPr)

LEFT JOIN

Make (KEEP=IndIO IndOut)

ON use.IndIO = make.IndIO;

run;

and got  740119 rows, but is greater than the number of observations in both datasets,

Lan

[Peter_C]

Repeatsof the join key on either table

[art297]

And, actually, I'm surprised you got so few.  When I ran your code I got: 847,103

Also, replace the "run" statement with a "quit" statement.

[LanMin]

Thank you , Arther.

[Tom]

You have multiple records for the same value of the key (IndIO) in the RIGHT table (Make).  So if in the table MAKE there are 10 records with the same value of INDIO then every record from the LEFT table (use) with that value of INDIO will be replicated 10 time.

You might want to add the DISTINCT keyword after the SELECT keyword.

[LanMin]

Thank you ,  Tom.