03-04-2013 05:51 AM
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!
03-04-2013 06:15 AM
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.