BookmarkSubscribeRSS Feed
dakes
Obsidian | Level 7

Hi,

 

To give an overall description: I have a SQL Server table with 22535417 rows that take up 4,9GB, on SQL Server, that I would like to import into SAS VA.

 

The import procedure times out on me so I tried to create a SAS dataset on the same server first but the SAS Dataset is 71GB big, compared to only 4,9GB on the server.

 

Why does the size blow up that much and how can I prevent it?

 

Thank you for help!

11 REPLIES 11
Kurt_Bremser
Super User

If you have long character fields in the table, the compress=yes dataset option will be helpful.

SAS does not have an equivalent to the varchar datatype present in RDBMS systems, so all character variables are fixed length (and therefore often padded with lots of blanks, which the compress option takes care of).

dakes
Obsidian | Level 7

The table has 28 columns: 4 varchar, 6 timestamp columns and the rest is bigint. I was told that you shouldn't compress tables when you plan on using them in SAS VA, is that advice outdated?

Kurt_Bremser
Super User

If the 4 varchar are defined with a large size (think 4K or similar), and mostly empty, you should do something about that. Use compress=yes in your Base SAS dataset, and decide if you need all that length in VA before uploading.

IMO, large texts are not something you would use in VA, where numbers, dates, categories are mostly the base for analysis.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Its quite hard to tell as we can't see any of the process.  Is it really "all in one table" on the server, or does it follow RDBMS and split things out?  This could be one main factor for size growth, if you combining all the data into one.

dakes
Obsidian | Level 7

Sql server tells me the table is 5006360 KB big:

 

2
3
4
USE {database_name};  
GO  
EXEC sp_spaceused N'{dbo}.{table_name}';  
GO  

 

It seems strange that it would grow that much.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

That only tells you the amount of disk space as defined in the database.  Databases have numerous tricks to shrink data, or speed up access.  

Have you tried exporting the data to plain text file format, just to see how big the plain text file would be, I suspect that will be quite large as well.  Maybe worth checking and seeing if the text file looks the same as the dataset.

dakes
Obsidian | Level 7

I just did an export to csv and the resulting csv file is 5.5gb big.

Kurt_Bremser
Super User

@dakes wrote:

I just did an export to csv and the resulting csv file is 5.5gb big.


That's because the export to csv removes all the trailing blanks from the varchar's (which is basically what the compress=yes option does with the dataset).

dakes
Obsidian | Level 7

I removed 2 of the varchar columns and used the compress=yes option. The resulting dataset is 3,9gb big, that's ok for me, I will now try and see what the performance in sas va is like. Thank you very much!

 

 

ChrisHemedinger
Community Manager

If you need to shrink it more, here's an EG task (and link to a program) that can help.  It analyzes your char data to learn what lengths you really need.

 

And another trick.  If you have character columns that are long, but categorical (not so many distinct values), you can replace those with a lookup value and a SAS format.  That trick works in Visual Analytics too and can reduce the memory footprint of your tables.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1195 views
  • 0 likes
  • 4 in conversation