08-23-2016 09:03 AM
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?
08-23-2016 09:26 AM
Not sure if I understand.
Isn't the restriction to 50 chars in the stored procedure? Or where can your get longer strings...?
08-23-2016 12:02 PM
the code is this
CONNECT TO odbc(dsn=Mydsn);
CREATE mytable AS SELECT * FROM CONNECTION TO odbc
DISCONNECT FROM odbc;
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?
08-24-2016 01:13 AM
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.
08-24-2016 05:55 AM
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;
08-24-2016 10:09 AM - edited 08-24-2016 10:45 AM
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?
08-24-2016 09:49 PM
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 .
08-25-2016 07:54 AM