BookmarkSubscribeRSS Feed
rwnj
Calcite | Level 5

I'm using Proc Export to export a large (53gb, and compression option = yes) .sas7bdat to .csv format.

I'm confused that SAS begins to hog all of my RAM: I went from 2-3 of 16 gb being used to 15.7 of 16 gb used.

Any ideas why SAS requires so much RAM to save a .csv? Also, I know there are multiple ways to save .sas7bdat's to .csv, Is there another way to save this file that is less RAM intensive?

13 REPLIES 13
Tom
Super User Tom
Super User

No need to use EXPORT to generate a CSV file from SAS dataset. Just write it using a normal data step.  This will not use much memory.

data _null_;

   set mydataset ;

   file 'myfile.csv' dsd lrecl=2000000 ;

   put (_all_) (:) ;

run;

If you want to write the column headers there are a number of solutions.  One that I find useful is to use PROC EXPORT, but use OBS=0 dataset option to write just the column headers and then use the above data step with the MOD option on the FILE statement to append the data.

Mit
Calcite | Level 5 Mit
Calcite | Level 5

Hi Tom

I use proc export as well to get a csv data. But sometimes I don't get to see all the columns exported. There are not too many rows though. I have about 3000 columns. Do you think the data step would help to sort my problem as well?

Thanks

Mit

sam369
Obsidian | Level 7

Hi Tom,

Instead of using EXPORT, your datastep approach is very fast even for the big files too. but i was stumped at the later part of yours

"One that I find useful is to use PROC EXPORT, but use OBS=0 dataset option to write just the column headers and then use the above data step with the MOD option on the FILE statement to append the data"

Could you able to show me with an example if possible how to use MOD option to append the data?

Thanks

Sam

sam369
Obsidian | Level 7

Thank you Tom!!!!

ballardw
Super User

Guessing here, but SAS will attempt to put entire datasets into memory for use "later". Which is helpful with sets that don't stretch your hardware for speeding multiple uses of the data.

SASKiwi
PROC Star

PROC EXPORT contains behind the scenes logic for building the SAS code necessary for the CSV. My suspicion is that this contributes to the memory overhead as well as the actual data.

Tom
Super User Tom
Super User

Could be.  I believe that PROC EXPORT will only create CSV files with maximum line length of 32,767. So for 3000 variables if the average value is longer than 10 you will get truncation or wrapping of lines.  If you write your own data step (or recall the one that PROC EXPORT generates and modify it) you can use a larger value for the LRECL option on the FILE statement.

Mit
Calcite | Level 5 Mit
Calcite | Level 5

Thanks Tom. Yes to create csv using Proc Export I believe you can send upto 2GB data.

LRECL is for number of records? My number of recors is max of 5000 lines

Regards,

Mit

Tom
Super User Tom
Super User

LRECL is Logical RECord Length.  It is how many characters to allow per line of data in a text file.  So if in one observation the sum of lengths of the displayed values for all variables (including the commas and any quoting required to mask embedded commas or quotes) is longer than the LRECL specified in the FILE statement then SAS will wrap the lines and cause your CSV file to be confused.  I am not sure what the max value that SAS supports for LRECL, but there is normally no harm in setting it much longer than you expect to use.  Unless you set RECFM (RECord ForMat) to F (Fixed).

The number of observations (rows or lines) is not limited, other than perhaps by your disk space.

Mit
Calcite | Level 5 Mit
Calcite | Level 5

Thanks Tom.

elgato
Calcite | Level 5

This was driving me crazy too.  Here's is the official SAS statement:

 

"Beginning in SAS 9.2 PROC EXPORT and the EXPORT WIZARD limit the record length of an external file to 32767. At this time there is not a way to increase the LRECL= value to be greater than 32767."

 

There are two workarounds, I think using ods for my data is insane so hopefully the macro will work.

 

http://support.sas.com/kb/40/906.html

dg19
Calcite | Level 5

🙂

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 13 replies
  • 28682 views
  • 0 likes
  • 8 in conversation