Exporting large number of variables to CSV

[mjsorrondegui]

I am trying to export a sas dataset to a CSV file. The dataset has 4,048 variables and 57,868 records. All the variables are exported correctly, but for some reason, the header gets truncated. Only the first 3,366 names are exported to the first records. Any idea why? This is how the header looks Var4570Post,,,

A few commas were added to the end, not sure why, but I should have another 682 variables in the header record. 

Any clue?? Is there a limit of variables to export? Should I try another method instead of proc export? Suggestions? Thanks in advance!!

[Reeza · Accepted solution]

It's using the default LRECL which is cutting off. Try a manual export instead:

https://communities.sas.com/t5/SAS-Procedures/Proc-Export-for-large-files/td-p/188894

Not sure if ODS CSV would be helpful here. 

[Kurt_Bremser]

There's a problem with proc export and lots of variables, as the put statement for the header exceeds the maximum length of a SAS statement (32767).

Create a dataset of variable names from dictionary.columns (or sashelp.vcolumn), and use call execute in a data _null_ step off that dataset to create your export data step dynamically.

[RW9]

Another thought, could you not re-structure your data?  It sounds like you have a normalised dataset - i.e. data goes across (as 57k records is not much but 4k variables is).  If I took this data:

ID   WEEK1    WEEK2    WEEK3...

1     34            12            56

And re-structured it to:

ID   WEEK_NO   RESULT

1     1                  34

1     2                  12 

1     3                  56

...

The data is effectively the same, however th import/export and programming work with this data is far simpler.   Then if I need a transposed dataset for output a simple proc transpose can achieve this.

[Patrick]

@mjsorrondegui

As others already pointed out there is a limitation how many characters of header Proc Export can write.

As @Reeza already hinted using ODS could be an alternative.

Below code worked for me:

data have;
  firstVar='AAAA';
  array allMyVars_0123456789012345_ {4048} 3. (4048*1);
/*  do i=1 to 57868;*/
  do i=1 to 100;
    output;
  end;
  stop;
run;


ods _all_ close;
ods tagsets.csv  file='c:\temp\test.csv';
proc print data=have noobs; 
run;
ods csv close;
ods listing;

[art297]

While the ODS method works, the following data step method worked as well AND ran about 500 times faster:

%let flname=have;
%let outfname=/folders/myfolders/testit3.csv;

proc transpose data=sashelp.vcolumn (where=(libname eq upcase("WORK") and
                                     memname eq upcase("&flname.")))
               out=vcolumnt (drop=_NAME_ _LABEL_);
  var name;
run;

data _null_;
  file "&outfname." dlm=',' dsd lrecl=2000000;
  set vcolumnt;
  put (_all_) (+0);
run;

data _null_;
  file "&outfname." dlm=',' dsd lrecl=2000000 mod;
  set &flname.;
  /* format _all_; */
  put (_all_) (+0);
run;

Art, CEO, AnalystFinder.com

[Patrick]

@art297

The advantage of using ODS is that it also does the quoting of character variables inclusive treatment of embedded quotes in a string.

Another option would be utility macro %ds2csv()

http://support.sas.com/documentation/cdl/en/lebaseutilref/64791/HTML/default/viewer.htm#n0yo3bszlrh0...