Problem in merging using proc sql

[nazmul]

Hello Everyone,

I used the following code more than 1000 times to merge and the code never lets me down. However, now I am having a weird error sign which I can no way solve. 

My original code is as follows:

proc sql;
create table C1 as
select a.*, b.*
from connection a
LEFT JOIN F1 b on a.ISIN = b.ISIN and a.year=b.fyear;
quit;

I get the following log report:

Could you please help me in figuring out the problem? I appreciate your help.

[Onizuka]

I think (i'm not sure) that you can't say a.* AND b.*

Try may be,

proc sql;
create table C1 as
select *
from connection a, F1 b
where a.ISIN = b.ISIN
and a.year = b.year ;
quit ;

I don't have test this but may be it works to :

proc sql;
create table C1 as
select *
from connection a
LEFT JOIN F1 b on a.ISIN = b.ISIN and a.year=b.fyear ;
quit;

[PaigeMiller · Accepted solution]

CONNECTION is a special word in SQL that has a special meaning, and it cannot be the name of a SAS data set.

That's why the error message say it is expecting TO, you have to specify a connection TO a database.

[Onizuka]

---

@PaigeMiller wrote:

CONNECTION is a special word in SQL that has a special meaning, and it cannot be the name of a SAS data set.

That's why the error message say it is expecting TO, you have to specify a connection TO a database.

---

I didn't know it, thanks for the learn

[nazmul]

Thank you for the solution. It has been a great help and great learning experience

[Tom]

It is getting confused because of the CONNECTION TO keyword.

Just use a two level name

from work.connection a

Or a name literal

from 'connection'n a