Exporting large number of variables to CSV

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

Exporting large number of variables to CSV

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


Accepted Solutions
Solution
‎06-15-2017 06:54 PM
Grand Advisor
Posts: 17,358

Re: Exporting large number of variables to CSV

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. 

View solution in original post


All Replies
Solution
‎06-15-2017 06:54 PM
Grand Advisor
Posts: 17,358

Re: Exporting large number of variables to CSV

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. 

Esteemed Advisor
Posts: 6,661

Re: Exporting large number of variables to CSV

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: Exporting large number of variables to CSV

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.

Respected Advisor
Posts: 3,836

Re: Exporting large number of variables to CSV

@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;
Esteemed Advisor
Posts: 7,294

Re: Exporting large number of variables to CSV

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

 

Respected Advisor
Posts: 3,836

Re: Exporting large number of variables to CSV

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 151 views
  • 2 likes
  • 6 in conversation