Creating DB2 table

[JoaoM]

 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.

[Kurt_Bremser]

Did you try

data db2lib.test2;
set work.test;
keep var1;
run;

?

[JoaoM]

Same error

[LinusH]

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;

[Kurt_Bremser]

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]

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).