Help using Base SAS procedures

Proc Export for large files

Reply
Contributor
Posts: 27

Proc Export for large files

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?

Super User
Super User
Posts: 6,500

Re: Proc Export for large files

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_) (Smiley Happy ;

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.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Proc Export for large files

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

Regular Contributor
Posts: 168

Re: Proc Export for large files

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

Super User
Super User
Posts: 6,500

Re: Proc Export for large files

Regular Contributor
Posts: 168

Re: Proc Export for large files

Thank you Tom!!!!

Super User
Posts: 10,500

Re: Proc Export for large files

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.

Super User
Posts: 3,106

Re: Proc Export for large files

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.

Super User
Super User
Posts: 6,500

Re: Proc Export for large files

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.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Proc Export for large files

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

Super User
Super User
Posts: 6,500

Re: Proc Export for large files

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.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Proc Export for large files

Thanks Tom.

Frequent Learner
Posts: 1

Re: Proc Export for large files

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

Ask a Question
Discussion stats
  • 12 replies
  • 7627 views
  • 0 likes
  • 7 in conversation