DATA Step, Macro, Functions and more

Truncation Varchar in passthrough

Reply
Occasional Contributor
Posts: 6

Truncation Varchar in passthrough

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.

Super User
Posts: 5,257

Re: Truncation Varchar in passthrough

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

Data never sleeps
Super User
Posts: 10,516

Re: Truncation Varchar in passthrough

May not hurt to show the code involved.

Occasional Contributor
Posts: 6

Re: Truncation Varchar in passthrough

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?

 

Super User
Posts: 10,516

Re: Truncation Varchar in passthrough

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

Super User
Posts: 9,682

Re: Truncation Varchar in passthrough

Not sure. libname's option ?

 dbmax_text=32767

Occasional Contributor
Posts: 6

Re: Truncation Varchar in passthrough

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?

Super User
Posts: 9,682

Re: Truncation Varchar in passthrough

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;

Occasional Contributor
Posts: 6

Re: Truncation Varchar in passthrough

[ Edited ]

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?

Super User
Posts: 9,682

Re: Truncation Varchar in passthrough

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 .
Occasional Contributor
Posts: 6

Re: Truncation Varchar in passthrough

Thanks,

 

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

 

Super User
Posts: 9,682

Re: Truncation Varchar in passthrough

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.
Ask a Question
Discussion stats
  • 11 replies
  • 378 views
  • 0 likes
  • 4 in conversation