SAS Export dataset as csv or excel preserving line break

Question

I am trying to export my dataset from SAS to excel either is csv or xls format however, when I do this the columns with line breaks messes up my excel. Is there a way to export SAS dataset to excel preserving line breaks? I also need to display labels instead of column names and the dataset is fairly large approx. 150,000 rows.

Here is what I did,

proc export data=Final_w_label
outfile='work/ExtractExcel.csv'
dbms=csv label replace;
run; quit;

Thank you in advance.

If you need Excel, export to Excel not CSV. A CSV file is a text file and SAS has little to no control over how it interprets things. ODS EXCEL is an option that's pretty powerful. — Reeza

Richard Richard · Accepted Answer · 2018-08-30T04:18:30

See the bottom of the post for sample data.

One effective way to create an export that Excel will open easily and display embedded newlines is to use XML.

libname xmlout xmlv2 'c:\temp\want.xml';
data xmlout.want;
  set have;
run;
libname xmlout;

In Excel (365) do File/Open, select the want.xml file and then select As an XML table in the secondary Open XML dialog that is raised.

Other ways

There are other ways to move SAS data into a form that Excel can parse. Proc EXPORT will create a text file with embedded carriage returns in the character variables (which Excel uses for in cell newlines)

proc export dbms=csv data=have label replace file='c:\temp\want.csv';
run;

The problem of the export is that Excel will not import the data properly using it's wizards. There might be a vbs solution for reading the export, but that is probably more trouble than worth.

Another form of export is dbms=excel that creates .xlsx files:

proc export dbms=excel data=have label replace file='c:\temp\want.xlsx';
run;

This export can be opened by Excel and the columns will all be correct. However, the initial view presentation of the data value in cells with embedded carriage returns will not appear to have the newline. Further examination with F2 edit mode will show that those embedded new lines are there, and pressing Enter (to accept edits) will cause the cell view to show the embedded newlines. You don't want to have to F2 every cell render as expected.

Sample Data

data have (label="Lines within stanza are separated by newline character");
  attrib 
    id length=8 label='Identity'
    name length=$50 label='Poem name'
    auth length=$50 label='Author'
    stanza1-stanza20 length=$250;
  ;

  array stz stanza:;

  id + 1;
  section = 1;

  infile cards eof=last;

  do while (1=1);
    linenum + 1;
    input;
    select;
      when (_infile_ = '--') leave;
      when (linenum = 1) name = _infile_;
      when (linenum = 2) auth = _infile_;
      when (_infile_ = '') section + 1;
      otherwise stz(section) = catx('0d'x, stz(section), _infile_);
    end;
  end;

last:

  output;

  datalines4;
Trees
Joyce Kilmer
I think that I shall never see
A poem lovely as a tree.

A tree whose hungry mouth is prest
Against the earth’s sweet flowing breast;

A tree that looks at God all day,
And lifts her leafy arms to pray;

A tree that may in Summer wear
A nest of robins in her hair;

Upon whose bosom snow has lain;
Who intimately lives with rain.

Poems are made by fools like me,
But only God can make a tree.
--
;;;;
run;

SAS Export dataset as csv or excel preserving line break

1 Answers