topic Re: Setting Field Attribute Length on Data Returned From Oracle Using Proc SQL in SAS Procedures

Setting Field Attribute Length on Data Returned From Oracle Using Proc SQL

JDMTX — Tue, 20 Apr 2010 22:12:02 GMT

How do I set a field attribute length using proc sql when I am using a pass through query to return data from Oracle.

Example SQL Code

proc sql;
connect to odbc(user=xxx password=xxx datasrc=Oracle);
create view rollup_vw as select * from connection to odbc
(SELECT T1.YEAR,
T1.Code,
T1.DESCRIPTION
FROM MIDF.Organization T1
quit;

The field code comes in as character with a length of 100 and I need to make it character with a length of 4. Currently using a data step with an attribute statment to change the length but it throws a warning about data loss and I need to either surpress the warning and associated warning codes or correct the data length in the proc sql. Any help would be greatly appreciated.

Many Thanks

Re: Setting Field Attribute Length on Data Returned From Oracle Using Proc SQL

Patrick — Wed, 21 Apr 2010 00:44:16 GMT

Simply use Oracle's substr() function:

proc sql;
connect to odbc(user=xxx password=xxx datasrc=Oracle);
create view rollup_vw as select * from connection to odbc
(SELECT T1.YEAR,
T1.Code,
substr(T1.DESCRIPTION,1,4) as DESCRIPTION
FROM MIDF.Organization T1
quit;

Re: Setting Field Attribute Length on Data Returned From Oracle Using Proc SQL

deleted_user — Wed, 21 Apr 2010 18:23:28 GMT

We tried to substr (...1,100) and ended up with lengths of 300 in the view.
Has this ever happened to you?

Re: Setting Field Attribute Length on Data Returned From Oracle Using Proc SQL

SASKiwi — Wed, 21 Apr 2010 23:44:46 GMT

Try this:

proc sql;
connect to odbc(user=xxx password=xxx datasrc=Oracle);
create view rollup_vw as select
year length = 4
,code
,description
from connection to odbc
(SELECT T1.YEAR,
T1.Code,
T1.DESCRIPTION
FROM MIDF.Organization T1
quit;

Re: Setting Field Attribute Length on Data Returned From Oracle Using Proc SQL

sbb — Thu, 22 Apr 2010 01:17:06 GMT

Of course the LENGTH= and/or FORMAT= parameters can be coded for individual SAS variables/columns in the SELECT. For SAS CHARACTER variables, remember to include the leading $ character, as in LENGTH=$4 .

Otherwise, the coding technique from the OP with "SELECT * " will not support any LENGTH= or FORMAT=, unless variables are listed explicitly.

Scott Barry
SBBWorks, Inc.