topic Re: Truncation Varchar in passthrough in SAS Programming

Truncation Varchar in passthrough

AsSASsin — Tue, 23 Aug 2016 13:03:22 GMT

Hello friends,

I'm trying to create a dataset with an explicit pass through.

The DB is a cachè DB from Intersystems but I have only a stored procedure that return a table.

Every field in the table obtained by stored procedure is a VarChar(50).

Every field in select * from connection to odbc (...) returned in the SAS dataset has length, informat and format = 50.

But I have some fields more than length = 50.

How could I resolve this issue with the stored procedure query?

Thanks.

Re: Truncation Varchar in passthrough

LinusH — Tue, 23 Aug 2016 13:26:33 GMT

Not sure if I understand.

Isn't the restriction to 50 chars in the stored procedure? Or where can your get longer strings...?

Please elaborate...

Re: Truncation Varchar in passthrough

ballardw — Tue, 23 Aug 2016 15:04:12 GMT

May not hurt to show the code involved.

Re: Truncation Varchar in passthrough

AsSASsin — Tue, 23 Aug 2016 16:02:06 GMT

Hello,

the code is this

PROC SQL;

CONNECT TO odbc(dsn=Mydsn);

CREATE mytable AS SELECT * FROM CONNECTION TO odbc

(

call InterSystems_storedProc

);

DISCONNECT FROM odbc;

QUIT;

The "call" command give me a table with the field's lengths of the varchar in the database (every field returned by the stored procedure is varchar (50) ).

Some fields have the length = 52, but my sas dataset take the length = 50 with 2 bytes truncation.

Is there a solution to avoid truncation and set the length with a greater value?

Re: Truncation Varchar in passthrough

Ksharp — Wed, 24 Aug 2016 03:06:29 GMT

Not sure. libname's option ?

 dbmax_text=32767

Re: Truncation Varchar in passthrough

AsSASsin — Wed, 24 Aug 2016 05:13:58 GMT

I tried dbmax_text but nothing, this option doesn't work.

I inserted dbmax_text option after dsn...tried with fields in select setting length, informat and format but nothing.

Any suggestion?

Re: Truncation Varchar in passthrough

Ksharp — Wed, 24 Aug 2016 09:55:00 GMT

Ha. Maybe you import that table correctly, but FORMAT is wrong , Try assign another format like $200.

or remove all the format and see what happened.

proc datasets library=work nolist nodetails;
 modify have;
 attrib _all_ format= ;
quit;

Re: Truncation Varchar in passthrough

AsSASsin — Wed, 24 Aug 2016 14:45:41 GMT

Hi! Thank you for your reply.

I tried with your suggestion, but it is the same.

Some variables are truncated...I think that this happens inside the pass through...assign format or informat after that the SAS table is made is useless.

Is it possible that a pass through hasn't an option for the varchar types?

Re: Truncation Varchar in passthrough

ballardw — Wed, 24 Aug 2016 15:38:54 GMT

Can you show the results of Proc Contents on the Mytable SAS dataset?

Re: Truncation Varchar in passthrough

Ksharp — Thu, 25 Aug 2016 01:49:59 GMT

1)
That is really weird. Did you get any warning msg in LOG ? 
or Can you copy a brand new table without format , label ..... 
Like:
create table new as
select * from old

 and import again ,see what happen .


2)
Alternative way to expand the length of variable is using CVP engine :

libname x cvp 'c:\temp\';
proc copy in=DB out=x noclone;
 select old_table;
run;




And It is a good time to talk with sas support .

Re: Truncation Varchar in passthrough

AsSASsin — Thu, 25 Aug 2016 08:50:13 GMT

Thanks,

could you tell me how can I contact the SAS support?

Re: Truncation Varchar in passthrough

Ksharp — Thu, 25 Aug 2016 11:54:38 GMT

I remember there is a URL in support.sas.com , which you can use to report your question. Also you could talk to your local SAS agent. That might give you a little fast.