SAS and Oracle CLOB unwanted conversion

Question

We have some SAS programs and they used to run fine and our old Oracle database.

We've migrated to a new Oracle DB on Amazon RDS (I don't know if it's relevant) and a new SAS Server instance (from 9.1 to 9.3, don't know if relevant). When running our programs, we are constantly facing the issue when string types, when uploaded to Oracle with a proc sql or data step, are randomly (?) converted to CLOB or LOB. Our strings do not exceed the maximum length authorized by Oracle (for the varchar type), they are pretty short actual, but still, the data gets uploaded to CLOB. That affects our whole process for reading the data.

We have found this workaround but I'm not a fan:

data oracledb.new_data;
length REGION $ 50;
format REGION $char50.;
set old_data;
run;

The fact is, many, many string columns get randomly converted to CLOB

Do you know how to solve that issue? Does it come from the Oracle side (I doubt it) or from the SAS side (but what has changed?)

Thanks for your help, I hope I have provided you with enough information

Stephane Stephane · Accepted Answer · 2014-08-06T13:06:06

Ok found the error.

While transforming the data, if you don't specify the output type, the SAS datasets can automatically inherit long, very long formats.

Here is the fix: http://support.sas.com/kb/24/804.html

This will reduce the length to the minimum without losing data. When uploading to Oracle, the formats will be adapted.

NB: I just reduced the size of my dataset by 80%. I recommend this macro to everyone.

Follow up question (please answer in comments): This changes only the format, not the informat. Is that an issue, can it affect performance?

Thanks!

SAS and Oracle CLOB unwanted conversion

1 Answers