11-03-2015 11:15 PM
For data, I know how to deal with leading zeros and excelXP, but what if the label has a leading zero?
I tried changing the label statement to ...
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.
11-04-2015 05:30 AM
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;
11-04-2015 07:51 AM - edited 11-04-2015 08:03 AM
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:
ods _all_ close;
ods tagsets.ExcelXP file='C:\temp\test.xml' style=Printer;
proc report data=test nowd;
*** That is type<colon>String below ;
define x / style(header)=[tagattr='typetring format:@'];
ods tagsets.ExcelXP close;
11-04-2015 08:26 AM
Without the smily face, here is the code.
ods _sll_ close;
ods tagsets.excelxp file="\\space.xml";
proc report data=tmp nowd;
define a / style(header)=[tagattr='type:string format:@'];
ods tagsets.excelxp close;