topic Re: ODS ExcelXP and Label with a Leading Zero in ODS and Base Reporting

ODS ExcelXP and Label with a Leading Zero

KimLeBouton — Wed, 04 Nov 2015 04:15:30 GMT

For data, I know how to deal with leading zeros and excelXP, but what if the label has a leading zero?

data test;

x=5;

label x=01234;

run;

I tried changing the label statement to ...

label x="'01234";

but the label retains the single quote.

I'm using PROC REPORT. If there is a solution with PROC PRINT, I can easily change out the code to PROC PRINT.

TIA,

Kim LeBouton

Re: ODS ExcelXP and Label with a Leading Zero

RW9 — Wed, 04 Nov 2015 10:30:08 GMT

Could you give me an example of where you would want to do this? To my mind a column heading of just numeric would be invalid in most scenarios (i.e. variable name), so why expect it to work in Excel? One option I suppose you could try, is to put a special charcter in:

In this example I use a special hidden character (an empty space for this purpose), by calling the byte() function. Because this is a text character Excel cannot convert it to number, so it forces the text format, hence keeping your prefix 0. As I said above this is not the optimal approach, labels should have characters in them, and this may fall over if you use this for anything else as they are special characters:

data tmp;
  A="ABC";
run;

ods escapechar="^";
ods tagsets.excelxp file="s:\temp\rob\space.xls";
proc report data=tmp nowd;
  columns a;
  define a / "%sysfunc(byte(161))01234";
run;
ods tagsets.excelxp close;

Re: ODS ExcelXP and Label with a Leading Zero

Vince_SAS — Wed, 04 Nov 2015 13:03:24 GMT

I haven't seen this before but the problem with the column heading is basically the same as that of having a leading zero in a data cell: it's typed as a numeric. You see this in the XML:

What you want is this:

Here's a solution:

data test;

x=5;

label x='01234';

run;

ods _all_ close;

ods tagsets.ExcelXP file='C:\temp\test.xml' style=Printer;

proc report data=test nowd;

column x;

*** That is type<colon>String below ;

define x / style(header)=[tagattr='type:String format:@'];

run; quit;

ods tagsets.ExcelXP close;

Vince DelGobbo

SAS R&D

Re: ODS ExcelXP and Label with a Leading Zero

KimLeBouton — Wed, 04 Nov 2015 13:26:37 GMT

Thanks.

Without the smily face, here is the code.

data tmp;

A="ABC";

label A='01234';

run;

ods _sll_ close;

ods escapechar="^";

ods tagsets.excelxp file="\\space.xml";

proc report data=tmp nowd;

columns a;

define a / style(header)=[tagattr='type:string format:@'];

run;

ods tagsets.excelxp close;