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;
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.
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.
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.