DATA Step, Macro, Functions and more

proc sql merge -error

Reply
Frequent Contributor
Posts: 102

proc sql merge -error

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

Attachment
Attachment
PROC Star
Posts: 7,363

Re: proc sql merge -error

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

Frequent Contributor
Posts: 102

Re: proc sql merge -error

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

Valued Guide
Posts: 2,175

Re: proc sql merge -error

Repeatsof the join key on either table

PROC Star
Posts: 7,363

Re: proc sql merge -error

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.

Frequent Contributor
Posts: 102

Re: proc sql merge -error

Thank you , Arther.

Super User
Super User
Posts: 6,500

Re: proc sql merge -error

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.

Frequent Contributor
Posts: 102

Re: proc sql merge -error

Thank you ,  Tom.

Ask a Question
Discussion stats
  • 7 replies
  • 331 views
  • 0 likes
  • 4 in conversation