Hi everyone,

I've encountered a problem with Proc Export. I've got a large dataset in SAS with thousands of columns. I want to export it so I can use it in another program, but when I inspect the file, no matter the type I've exported to (XLS, CSV, TXT), the column names just disappear at around column 3500. Then, the data slowly starts to disappear. Rows at a time go missing, more and more, until just one row of data is left. This is definitely different from the data as it appears in SAS.

As noted above, I tried several different file types, but there was no change. I also tried opening the tab-delimited file in a text editor, thinking maybe it was an Excel problem, and again, the column names just disappear at the same place. I receive no error from SAS during the process of the export - it acts like everything went fine.

Any ideas? I'd really like to get this data out, and especially in this exact configuration (ie no converting to long instead of wide).

My SAS version is 9.2.

Thanks all.

asked 17 Jul '11, 00:18

Samantha's gravatar image

Samantha
5115
accept rate: 100%


It looks like you're running into the limit set on the LRECL option.

The following code will give you about 5.7K columns into a csv:

%macro this;
    data this;
        format x: $4.;
        %do i = 0 %to 9;
            %do j = 0 %to 999;
                x&i.&j. = cats(byte(65+&i.),&j.);
            %end;
        %end;
    run;
%mend this;
%this;

proc export data=work.this
            dbms=csv
            outfile=c:\\temp\\test_export.csv
            replace
; 
run;

The following notes are issued to the log (after around 40K other lines!!):

NOTE: 2 records were written to the file 'c:\\temp\\will\\test_export2.csv'.
      The minimum record length was 32764.
      The maximum record length was 32767.
NOTE: Some output data was ignored because the DROPOVER option was specified.

If we use an explicit filename, we can set the logical record length to accomodate the line lengths:

filename thisout "c:\\temp\\will\\test_export.csv" lrecl=65536

proc export
    data=work.this
    dbms=csv
    outfile=thisout
    replace
;
run;

This time, no notes are written to the log and all columns appear in the output.

It look to me like proc export is setting LRECL to MAX (aka 32K). This is a touch misleading, the maximum record length you can write is 1 GB for SAS9.2 (1MB for SAS9.1).

Hope this helps! - Will Dobson (who hasn't figured out how to sign into the site with is old account since it changed! ¬_¬)

link

answered 01 Aug '11, 06:41

WillDobson's gravatar image

WillDobson
111
accept rate: 0%

Thanks Will. (And sorry it's taken me so long to comment back. It seems it's been so long that RunSubmit doesn't even want me to sign in anymore, so I had to start new.) Weirdly, even though SAS seems to acknowledge the filename statement because I get the filename I specify in it, the LRECL statement appears to be skipped. I say this because after running your code above with the explicit filename, I get the same strangely dwindling CSV after exporting is done, and I get these notes in the log:

NOTE: The file MYDATAOUT is:
  Filename=test_export.csv,
  RECFM=V,LRECL=32767,File Size (bytes)=0,
  Last Modified=31Aug2011:14:14:41,
  Create Time=31Aug2011:14:14:41

NOTE: 61 records were written to the file MYDATAOUT.
  The minimum record length was 32761.
  The maximum record length was 32767.
NOTE: Some output data was ignored because the DROPOVER option was specified.

You can see that it's plain ignoring my LRECL option and telling me it's going to go with 32K instead. Any ideas as to why?

Edit: Will, I just tried with your created dataset and your export code, and still did not get a complete dataset. I got the same Notes in the Log about LRECL=32767. It seems obvious now that something is going on with my SAS, even though it is version 9.2. It really does not seem to be acknowledging the LRECL option, but I have no idea why.

Edit2: This seems to be an issue with 9.2. Will, are you using 9.1? I found mention here (http://www.mathkb.com/Uwe/Forum.aspx/sas/43808/SASware-Ballot-Change-LRECL) of this fix working with V9.1, but not with V9.2. How frustrating! I will open a ticket with SAS.

link

answered 31 Aug '11, 19:28

StillSamantha's gravatar image

StillSamantha
212
accept rate: 0%

edited 31 Aug '11, 22:26

To confirm your suspicians, I was indeed using 9.1

(20 Sep '11, 09:27) WilliamDobson

Hello all.

I received an answer from SAS, and it is ridiculous, but I think it worked.

Basically, the 32k limit for LRECL exists with PROC EXPORT in V9.2. I have no idea why. SAS told me that in order to get around this, I need to run the Data Step.

"The Data Step" is the massive, huge step that is spit out in the Log when you run Proc Export. Basically, the step that you are trying to avoid by running Proc Export in the first place. The step you are ESPECIALLY trying to avoid if you happen to have a dataset that needs an LRECL longer than 32k. Nonetheless...

You take this Data step from the Log. Of course, there are line numbers throughout it. Maybe you can turn these off, I'm not sure, but instead of bothering to find out, I saved the log to a text file using proc printto, then opened the text file in Excel, then adjusted the settings upon import so that all of the Line numbers were in Column A of the Excel sheet, with the rest of the stuff (the data step) in Column B. Then I deleted the line numbers.

Next, I copy/pasted that (apparently I'm stupid with Excel because whenever I tried to just save it as a text, it would put quotes around each line, whereas copy/paste the Excel column into Notepad gave me just the text of the datastep) into an Editor window in SAS. Now, the Data step calls the 'filename' statement that Will used above, so that must be maintained. So, the code will look like this (I deleted the huge, long, 10000s of lines of code for placing variables):

filename thisout "blah2.csv" lrecl=1000000000; /*this is the maximum LRECL value*/
data _null_;
set this; /*you don't need this line if you are exporting the last dataset in SAS's memory. it's not in the Log automatically. -S*/
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
%let _EFIREC_ = 0;     /* clear export record count macro variable */
file THISOUT delimiter=',' DSD DROPOVER lrecl=1000000000; /*hopefully, DROPOVER won't be put into action*/
if _n_ = 1 then        /* write column names or labels */
do;
put
        <snip! variable crap goes here>
end;
if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;

So, I ran that. First, SAS bombed out because I was out of memory. Rebooted, refrained from unnecessary memory gobbling programs upon startup, and ran it again.

I definitely got a much, much bigger file out of it, so big that I can't check it in Excel to make sure it's all there. I opened it in Notepad and it certainly looks all there, or at least more "all there" than it did before. A summary() and a fix() in R seems to indicate that the file is indeed all there.

So, should you have the misfortune of needing to get a HUGE file out of SAS V9.2, that's how you do it.

link

answered 01 Sep '11, 16:37

StillSamantha's gravatar image

StillSamantha
212
accept rate: 0%

Thanks for the research and update!

(20 Sep '11, 09:27) WilliamDobson

How about using ODS instead of proc export (if that's an option).

Here's an example using ODS csvall:

ods listing close;
ods csvall file="c:\\temp\\will\\test_export.ods.csv";
proc print
    data=work.this;
;
run;
ods csvall close;
ods listing;

This gives the full length of output (run against the example in my post above), and (in SAS9.1 at least) runs in about 1 second. For comparison, both the export steps above take between 5 to 7 minutes to run on my machine!

A few other options are detailed in this rather useful post: http://studysas.blogspot.com/2009/02/how-to-create-comma-separated-file-csv.html

Timings from both the dataset examples in the above post are also around 1 the second mark, so ODS export doesn't look too promising!

There are differences in the output of all these methods such as:

  • Spaces between values
  • Inclusion of header row
  • Inclusion of quote marks around items in header row

So you'd need to tweak options accordingly to ensure that the output is as required.

Hope this helps a bit more than the nightmare code from above!

-- Will

There

link

answered 20 Sep '11, 10:21

WilliamDobson's gravatar image

WilliamDobson
567114
accept rate: 36%

If somebody would like to run the timings and methods from this post in 9.2 and let me know the performance comparisons, I'd be interested to know!

(20 Sep '11, 10:22) WilliamDobson

Very helpful thread. I too was having problems exporting a file from SAS that had web links and associated file names with a variety of special characters. The ods export worked perfectly!

link

answered 19 Jul, 17:58

Kim%20Wilson's gravatar image

Kim Wilson
1
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×9
×3

Asked: 17 Jul '11, 00:18

Seen: 4,385 times

Last updated: 19 Jul, 17:58

powered by OSQA