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

[JDMTX]

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

[Patrick]

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;

[deleted_user]

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

[SASKiwi]

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;

[sbb]

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.