topic Re: Problem in merging using proc sql in SAS Programming

Problem in merging using proc sql

nazmul — Fri, 02 Aug 2019 08:45:53 GMT

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.

Re: Problem in merging using proc sql

Onizuka — Fri, 02 Aug 2019 12:25:44 GMT

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;

Re: Problem in merging using proc sql

PaigeMiller — Fri, 02 Aug 2019 12:29:49 GMT

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.

Re: Problem in merging using proc sql

Onizuka — Fri, 02 Aug 2019 12:38:53 GMT

@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

Re: Problem in merging using proc sql

Tom — Fri, 02 Aug 2019 13:00:02 GMT

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

Re: Problem in merging using proc sql

nazmul — Fri, 02 Aug 2019 15:45:01 GMT

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