PROC SQL, oracle data type problem

Reply
Occasional Contributor
Posts: 11

PROC SQL, oracle data type problem

Hi,

we have a problem with proc sql and oracle.

The data type in the database is varchar2(80). When reading the data with SAS, it is being stored as char 320.

This leads to huge files and long runtimes.

We are running this codes on Solaris 10 machines, NLS_LANG is set to American charset and SAS uses DBCS.

Does anyone of you know, what to do?

Thanks in advance!

Super User
Posts: 5,260

Re: PROC SQL, oracle data type problem

I'm not really familiar with how Oracle deals with NLS, but "American" sounds like a single byte representation. Having DBCS in SAS will the probably have each byte in Oracle doubles in SAS, which could explain 80 -> 160, but to 320, I don't know. A ticket to SAS tech support maybe?

And, do you need DBCS? It will blow up your char columns storage?

As for runtimes, try to keep the data within Oracle as much as possible, bt using SQL implicit/explicit pass-thru techniques.

If you need to keep the data in SAS, try to use compress, most char columns with such lengths doesn't use all 80 positions.

Data never sleeps
Ask a Question
Discussion stats
  • 1 reply
  • 159 views
  • 0 likes
  • 2 in conversation