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.
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...
May not hurt to show the code involved.
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?
Can you show the results of Proc Contents on the Mytable SAS dataset?
Not sure. libname's option ? dbmax_text=32767
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?
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;
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?
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 .
Thanks,
could you tell me how can I contact the SAS support?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.