Help using Base SAS procedures

Proc SQL connect to ODBC

Reply
Occasional Contributor J_C
Occasional Contributor
Posts: 18

Proc SQL connect to ODBC

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;

Super User
Posts: 3,250

Re: Proc SQL connect to ODBC

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;

 

 

Occasional Contributor J_C
Occasional Contributor
Posts: 18

Re: Proc SQL connect to ODBC

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.

Super User
Posts: 5,424

Re: Proc SQL connect to ODBC

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.

Data never sleeps
Super User
Posts: 3,250

Re: Proc SQL connect to ODBC

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

Super Contributor
Posts: 340

Re: Proc SQL connect to ODBC

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?

Ask a Question
Discussion stats
  • 5 replies
  • 695 views
  • 0 likes
  • 4 in conversation