topic Re: ERROR: The character variable char has too long a value for the DLIB library in New SAS User

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

milind3889 — Thu, 27 Jun 2019 22:55:27 GMT

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.

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

Reeza — Thu, 27 Jun 2019 23:22:01 GMT

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;

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

milind3889 — Fri, 28 Jun 2019 02:42:07 GMT

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

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

Ksharp — Fri, 28 Jun 2019 13:16:08 GMT

Try libname's option " dbmax_text=32767 " .

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

Ksharp — Fri, 28 Jun 2019 13:26:16 GMT

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;

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

Ksharp — Fri, 28 Jun 2019 13:25:22 GMT

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;

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

milind3889 — Fri, 28 Jun 2019 13:55:28 GMT

I tried with this option. It did not work.

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

milind3889 — Fri, 28 Jun 2019 13:57:34 GMT

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;

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

milind3889 — Fri, 28 Jun 2019 14:06:04 GMT

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

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

Ksharp — Fri, 28 Jun 2019 14:11:08 GMT

Use options

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

before PROC SQL and see what happened .

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

Ksharp — Fri, 28 Jun 2019 14:15:10 GMT

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

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

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

milind3889 — Fri, 28 Jun 2019 15:09:46 GMT

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

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

Reeza — Fri, 28 Jun 2019 15:36:51 GMT

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?

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

milind3889 — Fri, 28 Jun 2019 17:02:33 GMT

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

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

ballardw — Fri, 28 Jun 2019 18:30:54 GMT

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.

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

Ksharp — Sat, 29 Jun 2019 12:00:36 GMT

OK. Running the following code and see what happened.


data mtest2(drop=i);
do i=1 to 10;
aaaa=i;
bbbb=i*i;
xxxx="abc";
output;
end;
run;


options sastrace=',,,d' sastraceloc=saslog ;
proc sql;
create table dlib.mtest2 as
select * from mtest2;
quit;

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

milind3889 — Tue, 02 Jul 2019 15:32:02 GMT

I had tried this also. I got same error.

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

milind3889 — Tue, 02 Jul 2019 15:33:51 GMT

I got solution for this issue. This issues was related to latest version of redshift ODBC driver 1.4.

I uninstalled this driver and installed redshift ODBC drive 1.3. After this change, it worked well.

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

prashantpare — Mon, 22 Mar 2021 12:23:38 GMT

Hi,

Am facing the same issue with Redshift ODBC driver 1.4.2. And am unable to find the Redshift ODBC driver 1.3 to download anywhere on the AWS website. Any suggestions or link to download it?

Any other option that can help resolve the issue?

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

BarryParish2 — Tue, 04 May 2021 19:47:08 GMT

Same issue with same ERROR message. Could not resolve, but was able to get around using the below append method instead. Exact same data that return error message using original methods.

/* works */

proc datasets library= dbIndLab;
append base = wlslog3 data = work.testData2;
run;

/* error */

ERROR: The character variable category has too long a value for the DBINDLAB library.
proc sql;
INSERT INTO dbIndLab.wlslog3
SELECT * FROM work.testData3;
quit;

/* error */

ERROR: The character variable category has too long a value for the DBINDLAB library.
proc sql;
INSERT INTO dbIndLab.wlslog3 (id,category,type,servername,code,msg)
SELECT id,substr(category,25) as category,type,servername,code,msg FROM work.testData3;
quit;

/* error */

ERROR: The character variable category has too long a value for the DBINDLAB library.
data dbIndLab.wlslog4;
set work.testData3;
run;

/* also works */
proc sql;
INSERT INTO dbIndLab.wlslog3 (id,category,type,servername,code,msg)
VALUES (199,'categoryX','typeX','servernameX','codeX','msgX');
quit;