topic Re: Proc SQL connect to ODBC in SAS Procedures

Proc SQL connect to ODBC

J_C — Mon, 11 Jan 2016 22:00:00 GMT

The test connection is successful in the datasources (ODBC), but when I try uploading the table, it gives me:

ERROR: PROC SQL requires any created table to have at least 1 column.

I don;t understand this error.

proc sql;
connect to odbc (user=xxx password=xxx datasrc=xxx);
create table rwork.xxx_xxx as select * from connection to odbc (
SELECT *
FROM dbo.v_xxx_xxx
WHERE Period_Id=&date_ncr.
);
quit;

Re: Proc SQL connect to ODBC

SASKiwi — Mon, 11 Jan 2016 23:44:32 GMT

What do you mean by uploading the table? Does the SAS LIBREF RWORK refer to a SAS library or a non-SAS datasource?

If you run the following test does it give you at least one data column?

proc sql outobs = 10;
connect to odbc (user=xxx password=xxx datasrc=xxx);
select * from connection to odbc (
SELECT *
FROM dbo.v_xxx_xxx
WHERE Period_Id=&date_ncr.
);
quit;

Re: Proc SQL connect to ODBC

user24feb — Tue, 12 Jan 2016 07:52:09 GMT

It is possible to replicate this error for example:

Libname sqlstage ODBC Schema=DBO DSN=sqlstage;
Proc SQL;
  *Drop Table sqlstage.Test_x_3;
  Create Table sqlstage.Test_x_3 (Var Float);
  Create Table sqlstage.Test_x_4 As Select * From sqlstage.Test_x_3 (Drop=Var);
Quit;

Gives:

ERROR: Table SQLSTAGE.Test_x_3 doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.

Could it be that there actually are no columns or that the user simply hasn't the permission to access this table?

Re: Proc SQL connect to ODBC

J_C — Tue, 12 Jan 2016 13:23:10 GMT

SAS Kiwi,

we reach into a databse to pull in table dbo.v_xxx_xxx into a SAS dataset (this is what I meant by uploading). The SAS dataset we are creating from the dbo.v_xxx_xxx view will reside in a UNIX environement which is referenced by rwork. RWORK is defined and has always been working without any issues.

as for the sample you asked me to run (adding outobs=10), it provided me with the same error.

Thanks.

Re: Proc SQL connect to ODBC

LinusH — Tue, 12 Jan 2016 14:45:42 GMT

Whenever there is doubt about the data source, and you ave an explicit SQL - just try that SQL in the data source, using the same userid used in the connect to statement.

Re: Proc SQL connect to ODBC

SASKiwi — Tue, 12 Jan 2016 18:39:31 GMT

Please note my sample used select * not create table to try to avoid your error. Did you still get the same error?