ERROR: The character variable char has too long a value for the DLIB library

[milind3889]

When we upload SAS dataset with only numeric variable to redshift, it is uploaded successfully.

But when we upload SAS dataset with character variables to redshift, we are getting error as below.ERROR: The character variable char has too long a value for the DLIB library. 

We are using SAS/ACCESS Interface to ODBC for connection with redshift.

Please see below code.

libname dlib odbc user="milind" password="xxxxxxxxxxxxxxxxx"  
datasrc="RWE_pharmetrics_DSN" schema="public";

data mtest2(drop=i);
length no1 8 no2 8 char $8;
do i=1 to 10;
no1=i;
no2=i*i;
char="abc";
output;
end;
run;


data dlib.mtest2;
set mtest2;
run;

Please see below log.

2 libname dlib odbc user="milind" password=XXXXXXXXXXXXXXXXXXXXXX datasrc="RWE_pharmetrics_DSN"
2 ! schema="public";
NOTE: Libref DLIB was successfully assigned as follows:
Engine: ODBC
Physical Name: RWE_pharmetrics_DSN
3
4 data mtest2(drop=i);
5 length no1 8 no2 8 char $8;
6 do i=1 to 10;
7 no1=i;
8 no2=i*i;
9 char="abc";
10 output;
11 end;
12 run;

NOTE: The data set WORK.MTEST2 has 10 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

13
14
15 data dlib.mtest2;
16 set mtest2;
17 run;

ERROR: The character variable char has too long a value for the DLIB library.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set DLIB.MTEST2 was only partially opened and will not be saved.
WARNING: File deletion failed for DLIB.MTEST2.DATA.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.09 seconds

Please see below ODBC data source settings.

[Reeza]

Do you have a different Access engine you can try? I think SAS has redshift specifically if you have the license.

You can check your licenses with

proc setinit;run;

and installlation:

proc product_status;run;

[milind3889]

We dont have difference access engine. 

We have SAS/ACCESS Interface to ODBC license. We have use this product only,

(We don't have SAS/ACCESS Interface to Redshift).

[Ksharp]

Try libname's option "  dbmax_text=32767 " .

[milind3889]

I tried with this option. It did not work.

[milind3889]

I tried this. It did not work. Please see below log. 

88 libname dlib odbc user="milind" password=XXXXXXXXXXXXXXXXXXXXXX datasrc="RWE_pharmetrics_DSN"
88 ! schema="public" dbmax_text=32767 ;
NOTE: Libref DLIB was successfully assigned as follows:
Engine: ODBC
Physical Name: RWE_pharmetrics_DSN
89
90 data mtest2(drop=i);
91 length no1 8 no2 8 char $8;
92 do i=1 to 10;
93 no1=i;
94 no2=i*i;
95 char="abc";
96 output;
97 end;
98 run;

NOTE: The data set WORK.MTEST2 has 10 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

99
100
101 data dlib.mtest2;
102 set mtest2;
103 run;

ERROR: The character variable char has too long a value for the DLIB library.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set DLIB.MTEST2 was only partially opened and will not be saved.
WARNING: File deletion failed for DLIB.MTEST2.DATA.
NOTE: DATA statement used (Total process time):
real time 0.17 seconds
cpu time 0.14 seconds

[Ksharp]

Or may SQL is a better company .

proc sql;
create table dlib.mtest2 as
select * from mtest2;
quit;

/**********************************/
proc sql;
create table dlib.mtest2 like  mtest2;

insert into dlib.mtest2
select * from mtest2 ;
quit;

[milind3889]

I tried the same. 

Please see below log for Program1: 

106 libname dlib odbc user="milind" password=XXXXXXXXXXXXXXXXXXXXXX datasrc="RWE_pharmetrics_DSN"
106! schema="public";
NOTE: Libref DLIB was successfully assigned as follows:
Engine: ODBC
Physical Name: RWE_pharmetrics_DSN
107
108 proc sql;
109 create table dlib.mtest2 as
110 select * from mtest2;
ERROR: The character variable char has too long a value for the DLIB library.
WARNING: File deletion failed for DLIB.MTEST2.DATA.

111 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.15 seconds
cpu time 0.12 seconds

Please see below log for Program2:

122 libname dlib odbc user="milind" password=XXXXXXXXXXXXXXXXXXXXXX datasrc="RWE_pharmetrics_DSN"
122! schema="public";
NOTE: Libref DLIB was successfully assigned as follows:
Engine: ODBC
Physical Name: RWE_pharmetrics_DSN
123 /*
124 proc sql;
125 create table dlib.mtest2 as
126 select * from mtest2;
127 quit;
128 */
129 /**********************************/
130
131 proc sql;
132 create table dlib.mtest2 like mtest2;
ERROR: The character variable char has too long a value for the DLIB library.
WARNING: File deletion failed for DLIB.MTEST2.DATA.
133 insert into dlib.mtest2
134 select * from mtest2 ;
ERROR: File DLIB.MTEST2.DATA does not exist.
135 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.20 seconds
cpu time 0.12 seconds

[Ksharp]

Use options

option sastrace=',,,d' sastraceloc=saslog ;

before PROC SQL and see what happened . 

[Ksharp]

Oh. Maybe 'char' is a keyword for DB .

And change 'char' into any validate variable name like 'xxx' .

[Ksharp]

And use these option to check what real SQL passed into DB.

option sastrace=',,,d' sastraceloc=saslog ;

proc sql;
insert into have
 set sex='G';
quit;

[milind3889]

Please see below log with sastrace option.

171

172  options sastrace=',,,' sastraceloc=saslog;

173  libname dlib odbc user="milind" password=XXXXXXXXXXXXXXXXXXXXXX  datasrc="RWE_pharmetrics_DSN"

173! schema="public"  dbmax_text=32767 ;

NOTE: Libref DLIB was successfully assigned as follows:

      Engine:        ODBC

      Physical Name: RWE_pharmetrics_DSN

174

175  data mtest2(drop=i);

176  length no1 8 no2 8 char $8;

177  do i=1 to 10;

178  no1=i;

179  no2=i*i;

180  char="abc";

181  output;

182  end;

183  run;

NOTE: The data set WORK.MTEST2 has 10 observations and 3 variables.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

184

185

186  data dlib.mtest2;

187  set mtest2;

188  run;

ERROR: The character variable char has too long a value for the DLIB library.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set DLIB.MTEST2 was only partially opened and will not be saved.

WARNING: File deletion failed for DLIB.MTEST2.DATA.

NOTE: DATA statement used (Total process time):

      real time           0.14 seconds

      cpu time            0.09 seconds

[Reeza]

That still uses the variable named char. I'm surprised the dB allows you to have a variable with that name since it's usually a key word. Try using a different name and see what happens?

[milind3889]

Yes. I tried with different variable name. But no luck. Please see below error.

1
ODBC: AUTOCOMMIT is NO for connection 0 0 1877360424 tkvercn1 0 Submit
2 options sastrace=',,,d' sastraceloc=saslog;
3 libname dlib odbc user="milind" password=XXXXXXXXXXXXXXXXXXXXXX datasrc="RWE_pharmetrics_DSN"
3 ! schema="public";
NOTE: Libref DLIB was successfully assigned as follows:
Engine: ODBC
Physical Name: RWE_pharmetrics_DSN
4
5 data mtest2(drop=i);
6 length no1 8 no2 8 var1 $8;
7 do i=1 to 10;
8 no1=i;
9 no2=i*i;
10 var1="abc";
11 output;
12 end;
13 run;

NOTE: The data set WORK.MTEST2 has 10 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

14
15
16 data dlib.mtest3;
17 set mtest2;
18 run;

ODBC: AUTOCOMMIT is NO for connection 1 1 1877360424 tkvercn1 0 DATASTEP
ODBC: AUTOCOMMIT turned ON for connection id 1 2 1877360424 tkvercn1 0 DATASTEP
3 1877360424 tkvercn1 0 DATASTEP
ODBC_1: Prepared: on connection 1 4 1877360424 tkvercn1 0 DATASTEP
SELECT * FROM public.MTEST3 WHERE 0=1 5 1877360424 tkvercn1 0 DATASTEP
6 1877360424 tkvercn1 0 DATASTEP
ODBC: AUTOCOMMIT is NO for connection 2 7 1877360424 tkvercn1 0 DATASTEP
ERROR: The character variable var1 has too long a value for the DLIB library.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set DLIB.MTEST3 was only partially opened and will not be saved.
ODBC: ROLLBACK performed on connection 2. 8 1877360424 tkvercn1 0 DATASTEP
ODBC: COMMIT performed on connection 2. 9 1877360424 tkvercn1 0 DATASTEP
WARNING: File deletion failed for DLIB.MTEST3.DATA.
NOTE: DATA statement used (Total process time):
real time 0.15 seconds
cpu time 0.12 seconds

[ballardw]

Here is a possible change to your test data to consider:

data mtest2(drop=i);
length no1 8 no2 8 char $1;
do i=1 to 10;
no1=i;
no2=i*i;
char="a";
output;
end;
run;

I don't know the target data base at all but perhaps it uses CHAR internally to express a single character. That is a possible long shot for why you get "variable too long" errors.