Truncation Varchar in passthrough

[AsSASsin]

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.

[LinusH]

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

[ballardw]

May not hurt to show the code involved.

[AsSASsin]

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?

[ballardw]

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

[Ksharp]

Not sure. libname's option ?

 dbmax_text=32767

[AsSASsin]

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?

[Ksharp]

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;

[AsSASsin]

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?

[Ksharp]

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 .

[AsSASsin]

Thanks,

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

[Ksharp]

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.