topic Re: proc sql error in SAS Programming

proc sql error

woo — Wed, 18 Oct 2017 21:42:45 GMT

hello, I have below code where I am connecting from sas to hadoop and it is failing with below error and I am quite not sure what it is,

can someone please help,

10         proc sql outobs=100;
11         connect to impala (datasrc=xxx user=abc@.company.com pw=xxx database=whatever);
12         create table test as select * from connection to impala
13         (select var1,

var2,

                         var3
14             from whatver.table_name
15               where var3='2017-10-17'
16         );
ERROR: PROC SQL requires any created table to have at least 1 column.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
18         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.18 seconds
      cpu time            0.05 seconds

Re: proc sql error

ChrisBrooks — Thu, 19 Oct 2017 00:08:22 GMT

The first two things I'd check would be:

Does the user have permissions to access that table; and
Do any rows match the where clause

Re: proc sql error

woo — Thu, 19 Oct 2017 13:04:28 GMT

thanks,

yes, same query running fine from other sas server and output records.

Re: proc sql error

ChrisBrooks — Thu, 19 Oct 2017 13:53:51 GMT

Are you able to execute any other query to that DB and table?

Re: proc sql error

woo — Thu, 19 Oct 2017 14:00:17 GMT

if I run just connection, it seems it works fine,

4          proc sql outobs=100;
5          connect to impala as test2 (datasrc=xxx user=abc@.company.com pw=xxx database=whatever);
6          DISCONNECT FROM test2;
7
8          quit;
^L2                                                          The SAS System                     Thursday, October 19, 2017 09:56:00 AM

NOTE: PROCEDURE SQL used (Total process time):
real time 1.16 seconds
cpu time 0.04 seconds

Re: proc sql error

ChrisBrooks — Thu, 19 Oct 2017 23:36:40 GMT

Is var3 a date variable or is it character because it looks like you're treating it as character in the query which would be somewhat unusual?

Re: proc sql error

woo — Fri, 20 Oct 2017 03:22:14 GMT

i am pulling data from hadoop and have same query in different server with same configuration and its running fine.

however, i still believe i am missing some configuration on this server, not sure,

Re: proc sql error

LinusH — Fri, 20 Oct 2017 06:38:02 GMT

Have you looked at the SQLXRC and SQLXMSG macro variables?
Also, I can't anything on your query that requires an explicit pass through.
Use a libname instead, and set
Options msglevel=I sastrace=',,,d' saatraceloc=saslog nostsuffix;
Then Hive should feed back what's going on.

Re: proc sql error

woo — Fri, 20 Oct 2017 18:44:33 GMT

tried different table with libname statement,

options msglevel=I sastrace=',,,d' sastraceloc=saslog nostsuffix;

libname hdp impala dsn=xxx UID=abc PWD=whatever;

proc sql outobs=1000;
create table testlib.test as
(select var1,

var2,

var3

from hdp.table_name
where
var1 >= '2017-10-16'
and var2=<= '2017-10-17'
);

quit;
run;

Log,

NOTE: Libref HDP was successfully assigned as follows:
Engine: IMPALA

IMPALA_1: Prepared: on connection 0
SELECT * FROM `table_name`

ERROR: Table HDP.table_name doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
ERROR: Error trying to read from a DBMS table. At least one column must be selected.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
40
41 quit;

Re: proc sql error

woo — Thu, 26 Oct 2017 21:01:52 GMT

one more thing I found from /var/log/messages is,

sas: looking for plugins in '/opt/cloudera/impalaodbc/lib/64/sasl2', failed to open directory, error: No such file or directory

have these parameters from .odbc.ini file

[instance1]
Description=whatever
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so
Description=Cloudera ODBC Driver for Impala(64-bit) DSN
Host=abc.com
Port=xxxxx
Database=default
AuthMech=3
UseSASL=1
SSL=1
CAIssuedCertNamesMismatch=1
AllowSelfSignedServerCert=1
TrustedCerts=/opt/cloudera/impalaodbc/lib/64/cacerts.pem
TSaslTransportBufSize=2000
RowsFetchedPerBlock=10000
SocketTimeout=0
StringColumnLength=32767
UseNativeQuery=0