01-06-2014 12:43 PM
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?
01-06-2014 01:13 PM
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.
set mydataset ;
file 'myfile.csv' dsd lrecl=2000000 ;
put (_all_) ( ;
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.
01-06-2014 08:18 PM
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?
01-07-2014 10:14 AM
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?
01-06-2014 01:17 PM
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.
01-06-2014 01:47 PM
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.
01-06-2014 08:28 PM
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.
01-06-2014 08:35 PM
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
01-06-2014 09:04 PM
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.
07-08-2016 04:39 PM
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.