topic Re: Inline formatting swallows a space when going to Excel in ODS and Base Reporting

Inline formatting swallows a space when going to Excel

BenConner — Wed, 28 Jun 2017 17:54:59 GMT

Hi,

I'm testing applying formatting to parts of a data line when generating an Excel spreadsheet. The sample code below looks fine going to html but not to Excel--the 2nd test line has the space before Test drop out.

Can anyone shed light on why this might be happening?

Thanks!

--Ben

%let path=c:\temp\;
proc format;
value $dumbplan 'Second Test' = 'Second^{style[fontstyle=italic] Test}';
run;

data text;
input line $70.;
format line $dumbplan.;
cards;
First Test
Second Test
Third Test
;;;

ods escapechar='^';
ods excel
options(sheet_name='Test' sheet_label=' ')
file="&path.mytest.xlsx";

proc print data=text;
id line;
run;

ods excel close;

Re: Inline formatting swallows a space when going to Excel

Cynthia_sas — Sat, 01 Jul 2017 16:22:02 GMT

Hi, Ben:

I do observe this behavior when using SAS 9.4 and ODS EXCEL (losing the space between words). I don't know why it's happening, but it seems to me that you've run across something to be reported to Tech Support. I do NOT lose the space in the default HTML destination or in TAGSETS.EXCELXP. And, it doesn't seem to matter whether I make the variable in an assignment statement or using the format. As you can see, I changed your program a bit as a test.

cynthia

Re: Inline formatting swallows a space when going to Excel

BenConner — Sat, 01 Jul 2017 18:26:14 GMT

Thanks, Cynthia! Will run it by the TS folks and see what they say. I'll post a final comment here after they track it down.

Much appreciated!

--Ben

Re: Inline formatting swallows a space when going to Excel

BenConner — Sun, 02 Jul 2017 16:18:11 GMT

T.S. responded; this is a defect. With a cool workaround. 🙂

proc format;

value $dumbplan 'Second Test' = 'Second^{style[fontstyle=italic] ^{nbspace 2} Test}';

run;

Using the non-breaking space code will take care of it.

Is SAS doing 24x7 T.S. now?? Impressive.

--Ben