topic Re: ODS EXCEL - Line feeds in ODS and Base Reporting

ODS EXCEL - Line feeds

Gerd_64 — Fri, 11 Nov 2016 13:47:01 GMT

Hi,

I would like to export a file via ODS EXCEL and then reimport the new generated Excel file via PROC IMPORT. My problem is that ODS EXCEL wraps lines in the table cells if there is not enough space.This would be no problem but unfortunately the blanks get lost between the last word in the first line and the first word in the second line. So the values of the variables are not the same after PROC IMPORT. At the position of the blank now is a line feed (0A'x)

So I could replace this line feed with blank again after reimport. The problem is that line feeds will also be included if the wrapping is in the mid of a word because of lacking cell space. Afterwards I don't know, if there was a blank at the position of the line feed before or not.

Here is a test program:

filename mdata "c:\test.xlsx";

ods excel file=mdata;

ods excel options (sheet_label="TEST" sheet_name="TEST");

data test;

test1='ABCDEFGHIJKLMNOPQRSTUVWXABCDEFGHIJKLMNOPQRSTUVWXABCDEFGHIJKLMNOPQRSTUVWXABCDEFGHIJKLMNOPQRSTUVWX;

test2='ABCD EFGH IJKL MNOP QRST UVW XYZ';

test3='ABCD EFGH IJKL MNOP QRST UVW XYZ';

test4='ABCD EFGH IJKL MNOP QRST UVW XYZ';

test5='ABCD EFGH IJKL MNOP QRST UVW XYZ';

run;

proc report data=test nowindows;

column test1 test2 test3 test4 test5;

define test1 / display ;

define test2 / display ;

define test3 / display ;

define test4 / display ;

define test5 / display ;

run;

ods excel close;

proc import datafile="c:\test.xlsx" out=test dbms=xlsx replace;

sheet="TEST";

run;

Has anyone a solution for this or a workaround

Thanks

Gerd

Re: ODS EXCEL - Line feeds

Cynthia_sas — Fri, 11 Nov 2016 21:31:09 GMT

Hi:

Using a style override and TAGATTR specification of WRAP:NO, I was able to avoid getting any line feeds in the resulting XLSX file, as shown below. I made the data a bit smaller so the screen shot of the output and the imported data would fit with the code. But whether you have 3 variables or 20 variables, it seems that WRAP:NO as a style override prevented the text from wrapping.

Here's a really good paper about ODS EXCEL that compares some of the suboptions available with the new destination to the suboptions that were available with TAGSETS.EXCELXP destination.

http://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf p 12 has the WRAP:NO override.

cynthia

Re: ODS EXCEL - Line feeds

Gerd_64 — Mon, 14 Nov 2016 07:24:47 GMT

Hi Cynthia,

thank you very much for your response.

My problem is that I don't simply want SAS to make no text wrapping in Excel. I would like to transform SAS data into Excel, then make some data entry (maybe some rather long text entries, which should be wrapped in the Excel cells) and then reimport into SAS without losing any information, such as blanks which are replaced by line feeds.

Best regards,

Gerd

Re: ODS EXCEL - Line feeds

ballardw — Mon, 14 Nov 2016 12:11:51 GMT

Please be aware that if you have people doing data entry in Excel you are likely to have many issues with undesired values when they enter data with their own line feeds, tabs, unicode characters and possibly different language encodings (copy and paste can generate a multitude of issues).

Re: ODS EXCEL - Line feeds

Gerd_64 — Mon, 14 Nov 2016 12:52:56 GMT

Hi ballardw,

thank you for the comment. But we are only 3 or 4 people using this solution and would only enter normal ASCII text (no special characters and no copy and paste at all).

Best regards,

Gerd