ODS ExcelXP and Label with a Leading Zero

[KimLeBouton]

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

[RW9]

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;

[Vince_SAS]

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:

<Data ss:Type="Number">01234</Data></Cell>

What you want is this:

<Data ss:Type="String">01234</Data></Cell>

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

[KimLeBouton]

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;