topic Re: Exporting large number of variables to CSV in SAS Programming

Exporting large number of variables to CSV

mjsorrondegui — Thu, 15 Jun 2017 01:08:01 GMT

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!!

Re: Exporting large number of variables to CSV

Reeza — Thu, 15 Jun 2017 02:32:14 GMT

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.

Re: Exporting large number of variables to CSV

Kurt_Bremser — Thu, 15 Jun 2017 06:57:08 GMT

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.

Re: Exporting large number of variables to CSV

RW9 — Thu, 15 Jun 2017 08:21:15 GMT

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.

Re: Exporting large number of variables to CSV

Patrick — Thu, 15 Jun 2017 10:55:27 GMT

@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;

Re: Exporting large number of variables to CSV

art297 — Thu, 15 Jun 2017 15:53:13 GMT

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

Re: Exporting large number of variables to CSV

Patrick — Thu, 15 Jun 2017 22:56:35 GMT

@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#n0yo3bszlrh0byn1j4fxh4ndei8u.htm