BookmarkSubscribeRSS Feed
JDMTX
Fluorite | Level 6
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
4 REPLIES 4
Patrick
Opal | Level 21
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
Not applicable
We tried to substr (...1,100) and ended up with lengths of 300 in the view.
Has this ever happened to you?
SASKiwi
PROC Star
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2448 views
  • 0 likes
  • 5 in conversation