topic Re: proc sql merge -error in SAS Programming

proc sql merge -error

LanMin — Wed, 27 Nov 2013 21:12:20 GMT

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

Re: proc sql merge -error

art297 — Wed, 27 Nov 2013 21:34:43 GMT

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

Re: proc sql merge -error

LanMin — Wed, 27 Nov 2013 22:18:09 GMT

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

Re: proc sql merge -error

Peter_C — Wed, 27 Nov 2013 22:43:30 GMT

Repeatsof the join key on either table

Re: proc sql merge -error

art297 — Wed, 27 Nov 2013 22:48:56 GMT

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.

Re: proc sql merge -error

Tom — Fri, 29 Nov 2013 00:29:15 GMT

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.

Re: proc sql merge -error

LanMin — Fri, 29 Nov 2013 01:37:41 GMT

Thank you , Tom.

Re: proc sql merge -error

LanMin — Fri, 29 Nov 2013 01:37:52 GMT

Thank you , Arther.