Hi:
As I said, the FIRST requirement is to understand WHAT the mso-number-format value needs to be. I would NOT expect the sample code I gave you to work for analysis variables, because I was changing the mso-number-format for the CLASS variables, not for the analysis variables. Generally, analysis variables need to have the mso-number-format changed in the TABLE statement.
When I Googled around, I found this web page that shows how to make a custom format:
http://www.projectwoman.com/2007/06/millions-in-excel.html
So then, I went into Excel and typed some numbers into a sheet and tried this suggestion and indeed, the cell format in Excel, changed to show the M number.
Now, the reverse engineering starts. Seeing that the custom format -works- is good, but it still doesn't tell me what the format needs to be for
HTML.
So next, I saved the Excel file with the custom format file as an HTML file (which creates 1 "organizing" HTML file and a sub-directory of related files). So, I looked in the HTML file in the sub-directory (called sheet001.htm) and found the tag for my M number. Once I found that, I needed to look at the CLASS= attribute because that will point me to the right place in the CSS file that Excel built:
[pre]
<td class=xl73 width=109 style='border-top:none;border-left:none;width:82pt'>12,000M</td>
</tr>
[/pre]
So, THEN, I had to look down inside the CSS file to find this CSS section (in a file called stylesheet.css) to look for the mso-number-format:
[pre]
.xl73
{mso-style-parent:style0;
color:black;
font-size:12.0pt;
font-family:Arial, sans-serif;
mso-font-charset:0;
mso-number-format:"\#\#\,\#\#0\,\,\0022M\0022";
text-align:right;
border-top:.5pt solid black;
border-right:1.0pt solid black;
border-bottom:1.0pt solid black;
border-left:.5pt solid black;
background:lightgrey;
mso-pattern:black none;
white-space:normal;}
[/pre]
so the string I need is: mso-number-format:"\#\#\,\#\#0\,\,\0022M\0022"; but actually, since I'm going to quote the entire string in the HTMLSTYLE attribute for ODS, what I'm going to need for my style override is:
[pre]
htmlstyle='mso-number-format:\#\#\,\#\#0\,\,\0022M\0022'
[/pre]
Now, the good news is that you ONLY have to do that one time to find the custom format, but unless you know the HTML form of these custom formats off the top of your head, the only way I know to figure out the right
mso-number-format is to create your custom format in Excel, save the file as an HTML file and then look for the right mso-number-format that Excel places in the CSS file that goes along with the HTML file.
So, now, how to put the mso-number-format into PROC TABULATE. That part is relatively easy....for computed data cells (which is where your analysis need the M format), you would put the mso-number-format in the TABLE statement. (I showed my example with the CLASSLEV statement because at that point, I didn't know the right mso-number-format to use.
Here's code with a P12_cur_AMT variable and the MEAN statistic added. If you are sending a format to Excel, you really do NOT need the SAS format in the TABLE statement. I got rid of the SAS format from the TABLE statement and moved it to the PROC TABULATE statement. Also, to save real estate in the TABLE statement, I moved the ' ' for the label of MEAN and SUM into the KEYLABEL statement:
[pre]
data test2;
infile datalines;
input grp $ idval values;
p12_cur_AMT = values;
return;
datalines;
aaa 55 12000000000
aaa 55 11112223333
aaa 55 15131617167
bbb 66 15123456234
bbb 66 16111112222
bbb 66 17222223333
;
run;
** Use correct MSO-NUMBER-FORMAT string;
proc tabulate data=test2 f=comma25.2;
title '1) Excel should show ##,###M in cell and regular number in formula bar';
class grp;
var values P12_cur_AMT;
table grp=' ',
values*{s={htmlstyle='mso-number-format:\#\#\,\#\#0\,\,\0022M\0022'}}
P12_cur_AMT={LABEL='P12 Ave Current'}*Mean*{s={htmlstyle='mso-number-format:\#\#\,\#\#0\,\,\0022M\0022'}};
keylabel sum=' '
mean=' ';
run;
proc tabulate data=test2 f=comma25.2
style={htmlstyle='mso-number-format:\#\#\,\#\#0\,\,\0022M\0022'};
title '2) If ALL numbers in the data cells are Millions, then use style in TABULATE statement';
class grp;
var values P12_cur_AMT;
table grp=' ',
values
P12_cur_AMT={LABEL='P12 Ave Current'}*Mean;
keylabel sum=' '
mean=' ';
run;
ods _all_ close;
[/pre]
Do note the particular syntax of the style override in the TABLE statement. Those extra curly braces are absolutely required, as is the *. If you want the mso-number-format applied to more than 1 variable, then you will need to apply the format to EACH variable, as shown. Since you have only 4 variables out of many that need to have the Million format, you will not be able to use method #2, but method #1 in the code should work for you.
cynthia