BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASNE
Obsidian | Level 7

Hi Guys,

 

Currently i have a problem where i have retreiving data from a DB (around 10G) and save it as Sas7dat , but doing so the file size is 100G almost 10 times of its original file.

 

Do you guys have a solution for that ? thx in advance.

 

libname Folder 'C:\Sas';

 

proc sql.....create table sqltable as ....

Select a,b,c from A.Oracle .

........

 

data SAS.file;

set sqltable;

run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Most likely you have long character variables. SAS stores all strings as fixed length, padded with spaces. Most database systems, like Oracle, have a VARCHAR() type that does not store the trailing spaces.

 

If you do have long character variables then turning on COMPRESS dataset option will help.  But SAS will probably not compress as well as a true database engine could.

 

You can set the system option.

options compress=yes;

Or you could set it on the LIBNAME statement so that it is the default for all files written to that libref.

libname folder 'my path' compress=yes;
....
create table folder.mytable as 
....

Or use it as a dataset option instead to apply it to an individual dataset.

create table folder.mytable (compress=yes) as ...

Note that SAS uses a very simple compression algorithm. This means that for some tables the overhead added for implementing the compression will cause the table to be larger so check your SAS log.  When it writes the file it will note in the log the effect of the compression.

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

Most likely you have long character variables. SAS stores all strings as fixed length, padded with spaces. Most database systems, like Oracle, have a VARCHAR() type that does not store the trailing spaces.

 

If you do have long character variables then turning on COMPRESS dataset option will help.  But SAS will probably not compress as well as a true database engine could.

 

You can set the system option.

options compress=yes;

Or you could set it on the LIBNAME statement so that it is the default for all files written to that libref.

libname folder 'my path' compress=yes;
....
create table folder.mytable as 
....

Or use it as a dataset option instead to apply it to an individual dataset.

create table folder.mytable (compress=yes) as ...

Note that SAS uses a very simple compression algorithm. This means that for some tables the overhead added for implementing the compression will cause the table to be larger so check your SAS log.  When it writes the file it will note in the log the effect of the compression.

SASNE
Obsidian | Level 7

Thx Tom, you help me a lot.

 

Do you think if i save it as csv as a datasource/DB instead of sas7dat works ?

 

what is the drawback of doing so ?

 

thanks

ballardw
Super User

If you are going to do any analysis is SAS then if you saved that data as CSV instead of a SAS dataset then before any action on the data by SAS you would have to read it into SAS and create a SAS data set.

 

Whether saving the data as csv will work for other actions it really depends on what you may be attempting.

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
  • 3 replies
  • 2995 views
  • 4 likes
  • 3 in conversation