BookmarkSubscribeRSS Feed
JoaoM
Calcite | Level 5
 I'm trying to create a table in DB2, but the variable has 14 positions.
With variable with size below 9 I have no problems.
But when caught variables above that length I get the following error:
 
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "("
       was found following "".  Expected tokens may include:  "FIELDPROC CHECK GENERATED IMPLICITLY INLINE CONSTRAINT PRIMARY".  
       SQLSTATE=42601 .
 
 
Code:
 
proc sql;
    create table db2lib.test2 as
    select
     var1
 from
        work.test ;
quit;
 
 
max size of var1 is 99999999911119.
5 REPLIES 5
JoaoM
Calcite | Level 5

Same error

LinusH
Tourmaline | Level 20

Is it numerical or char?

If numerical, you may hit the default max size for the mapped data type in DB2.

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;
Data never sleeps
Kurt_Bremser
Super User

It looks like the column is (per default) defined as integer in DB/2, where you have a range of -2147483648 to +2147483647.

 

One thing that came to my mind was to use PROC DS2 and define the column as BIGINT or FLOAT.

LinusH
Tourmaline | Level 20

When numerical variable become this large, on can ask if they really need to be integers - are they par of calculations?

Another option is to use data set options to explicitly specify data types in the target database (DBTYPE).

Data never sleeps

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 3176 views
  • 1 like
  • 3 in conversation