p*****o
发帖数: 543 | 1 Hi all, I have a problem with PROC REPORT. I have one variable called ID,
and it is character variable, but it has leading zeros (such as: 0000123).
But when I was using the following define statement, in the final excel file
I created, it automatically removed leading zero and change it to be a
number. Any one can help me a lit bit on it?
Thanks,
define id/display 'ID' width = 10 format = $10.; |
l****u
发帖数: 529 | 2 try to add STYLE={HTMLSTYLE="VND.MS-EXCEL.NUMBERFORMAT:@"}. |
d******9
发帖数: 404 | |
d******9
发帖数: 404 | 4 I find the below option works:
data E;
input ID $ Name $;
cards;
0001 Tom
0003 Jack
6005 Gary
;
run;
filename AAA 'path/AAA.xls';
ODS listing;
ODS HTML file=Amanda
headtext="";
proc report data=E;
column ID name;
define id/display 'ID' width = 10 format = $10. ;
define name/display;
run;
ODS HTML close;
ODS listing; |
l****u
发帖数: 529 | 5 This one:define your_variable_name / display style(column)={tagattr='format:
##0.0'};?
This one: your_variable_name
/ style(column)={htmlstyle="mso-number-format:\@"} |
d******9
发帖数: 404 | 6 I tried both, they do not work. Why?
However, the Headtext option works
format:
【在 l****u 的大作中提到】
: This one:define your_variable_name / display style(column)={tagattr='format:
: ##0.0'};?
: This one: your_variable_name
: / style(column)={htmlstyle="mso-number-format:\@"}
|
l****u
发帖数: 529 | |
d******9
发帖数: 404 | 8 Finally, I find below info from SAS support.
Usage Note 32414: How can I prevent loss of leading zeroes when generating
Microsoft Excel output with ODS
http://support.sas.com/kb/32/414.html
Microsoft Excel uses the general format when importing data values that do
not have an Excel format applied. This general format attempts to determine
how the value should be formatted. For example, an account number that is
not alpha numeric and has leading zeroes will lose the leading zero. The
same problem occurs when you type a value in a cell of Excel.
To get around this problem, the Excel Text format can be applied so that the
value comes over the same way it was displayed in SAS. How this is done
depends on the ODS destination that you use to generate the file. If the
ExcelXP destination is used, the Excel text format can be applied using the
format: parameter within the TAGATTR= attribute. Using the MSOffice2k/HTML/
HTML3 destinations, the Microsoft Office CSS style property mso-number-
format can be used with the same text format. Select the Full Code tab to
see example code.
Syntax to prevent loss of leading zeros
data one;
input account name $;
cards;
023456 Bob
054556 Henry
034456 Wes
;
run;
/* ExcelXP destination */
ods tagsets.ExcelXP file="temp.xls";
proc print data=one;
var name;
var account / style(data)={tagattr="format:@"};
format account z6.;
run;
Ods tagsets.ExcelXP close;
/* MSOffice2k destination */
ods Msoffice2k file="temp1.xls";
proc print data=one;
var name;
var account / style(data)={htmlstyle="mso-number-format:\@"};
format account z6.;
run;
Ods msoffice2k close; |
d******9
发帖数: 404 | 9 I tried the options from SAS support, what I find is:
The above options work with tagsets.ExcelXP, MSOffice2k, but does not work
with ODS HTML.
Do not know why it failed with ODS HTML. |
