Help using Base SAS procedures

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

Reply
Contributor
Posts: 62

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

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
Respected Advisor
Posts: 3,890

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

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;
N/A
Posts: 0

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

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

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

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;
Super Contributor
Super Contributor
Posts: 3,174

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

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.
Ask a Question
Discussion stats
  • 4 replies
  • 422 views
  • 0 likes
  • 5 in conversation