BookmarkSubscribeRSS Feed
keV
Calcite | Level 5 keV
Calcite | Level 5

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

2 REPLIES 2
jakarman
Barite | Level 11

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 --<-----
keV
Calcite | Level 5 keV
Calcite | Level 5

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.

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1082 views
  • 2 likes
  • 2 in conversation