Architecting, installing and maintaining your SAS environment

Writing datasets using OleDB SQL queries. How to CREATE TABLE and compress string fields?

Reply
Occasional Contributor keV
Occasional Contributor
Posts: 7

Writing datasets using OleDB SQL queries. How to CREATE TABLE and compress string fields?

I am using the SAS IOM OleDB provider to create and populate SAS tables via execution of SQL statements.

All is working fine, but, for creation of string fields, I am defaulting to a length of 400 because I cannot know the maximum string length in advance.

This results in very large disk space usage for string fields.

One option is to somehow set COMPRESS = CHAR as part of my CREATE TABLE  execution, or some other command I can execute on the connection.

Does anyone know how to do this, or can suggest anything else to help?

Thanks

Kevin

Valued Guide
Posts: 3,208

Re: Writing datasets using OleDB SQL queries. How to CREATE TABLE and compress string fields?

SAS(R) 9.3 SQL Procedure User's Guide (dataset options). Did you try it coding as a SAS dataset option?

IT will do RLE optimizing causing some overhead in the CPU but of you have many of those the gaining in IO could be a far more better trade off.

---->-- ja karman --<-----
Occasional Contributor keV
Occasional Contributor
Posts: 7

Re: Writing datasets using OleDB SQL queries. How to CREATE TABLE and compress string fields?

I did not realise it was as simple as changing "CREATE TABLE (" to "CREATE table (compress=char, " into my CommandText.

On my test table, what was 312Mb is now 12 Mb (262,144 rows with 3 string fields). Very impressive size reduction, and it writes20%  faster too.

Ask a Question
Discussion stats
  • 2 replies
  • 381 views
  • 2 likes
  • 2 in conversation