The SAS Output Delivery System and reporting techniques

ODS ExcelXP and Label with a Leading Zero

Reply
Contributor
Posts: 52

ODS ExcelXP and Label with a Leading Zero

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: ODS ExcelXP and Label with a Leading Zero

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;
SAS Employee
Posts: 274

Re: ODS ExcelXP and Label with a Leading Zero

[ Edited ]

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='typeSmiley Frustratedtring format:@'];

  run; quit;

ods tagsets.ExcelXP close;

 

Vince DelGobbo

SAS R&D

Contributor
Posts: 52

Re: ODS ExcelXP and Label with a Leading Zero

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;

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