The SAS Output Delivery System and reporting techniques

ODS EXCEL - Line feeds

Reply
Occasional Contributor
Posts: 8

ODS EXCEL - Line feeds

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

 

SAS Super FREQ
Posts: 8,868

Re: ODS EXCEL - Line feeds

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

 

excel_wrap_no.png

Occasional Contributor
Posts: 8

Re: ODS EXCEL - Line feeds

Posted in reply to Cynthia_sas

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

Super User
Posts: 11,343

Re: ODS EXCEL - Line feeds

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

Occasional Contributor
Posts: 8

Re: ODS EXCEL - Line feeds

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

Ask a Question
Discussion stats
  • 4 replies
  • 550 views
  • 0 likes
  • 3 in conversation