BookmarkSubscribeRSS Feed
J_C
Calcite | Level 5 J_C
Calcite | Level 5

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;

5 REPLIES 5
SASKiwi
PROC Star

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;

 

 

J_C
Calcite | Level 5 J_C
Calcite | Level 5

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.

LinusH
Tourmaline | Level 20

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
SASKiwi
PROC Star

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

user24feb
Barite | Level 11

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1966 views
  • 0 likes
  • 4 in conversation