The SAS Output Delivery System and reporting techniques

DUAL FORMATS after exporting to excel

Reply
N/A
Posts: 0

DUAL FORMATS after exporting to excel

Hi,

I am currently having this problem, using these program

data tes;
infile datalines;
input values;
datalines;
12000000000;
;
run;

proc format;
picture test low-high = '000,000,000M' (Mult=.000001);
run;

the output will return a value of 12,000M. If I use the same format in a proc tabulate and export it to excel, the value will still have 12,000M. However, the problem is that the client after exporting the output of proc tabulate, they would like to see 12000M in the cell, however when highlighting the cell they will see 12000000000 in the formula tab.

Regards,
Axe
SAS Super FREQ
Posts: 8,740

Re: DUAL FORMATS after exporting to excel

Hi:
You do not show the code doing the "export" to Excel. How are you accomplishing the "export"?? Are you using PROC EXPORT?? Are you using the Output Delivery System?? And, if you are using ODS, are you using ODS CSV, ODS HTML or ODS TAGSETS.EXCELXP??

It is nearly impossible to answer your question without knowing this information.

cynthia
N/A
Posts: 0

Re: DUAL FORMATS after exporting to excel

Sorry cynthia... i am using ODS TAGSET.MSOFFICE2k.

My code is:

ods tagsets.msoffice2k file=< > style=< >;

proc tabulate;

run;

proc gplot;

run;

ods tagsets.msoffice2k close;
SAS Super FREQ
Posts: 8,740

Re: DUAL FORMATS after exporting to excel

Hi:
You did not say what version of SAS you were running. When I run this code in SAS 9.2, My MSOFFICE2K HTML file shows 12,000M in the body of the worksheet -AND- shows 12,000M up in the formula bar when they click in a cell. I am opening the file with Excel 2007.
[pre]
data tes;
infile datalines;
input grp $ idval values;
return;
datalines;
aaa 55 12000000000
bbb 66 15000000000
;
run;

proc format;
picture test low-high = '000,000,000M' (Mult=.000001);
run;

ods msoffice2k file='c:\temp\M_test_mso1.xls';

proc tabulate data=tes;
title 'Table 1 -- Excel will show whole number in formula bar and cell';
class grp;
var values;
table grp=' ',
values*f=12. ;
run;
ods _all_ close;
[/pre]

and this is what the underlying HTML tags contain:
[pre]
<th class="RowHeader" style=" text-align: left; vertical-align: top;" scope="row">aaa </th>
<td class="Data" style=" text-align: right; vertical-align: bottom;"> 12,000M</td>
[/pre]

Given this HTML tag, I don't see how your users could even see 12000000000 in the formula bar when they click in a cell, if you are going to use the SAS picture format for the value. That's because the SAS picture format changes the cell contents' representation in the HTML -- before Excel has a chance to open the file.

You need to investigate how to use the mso-number-format: CSS style property with an HTMLSTYLE override. With HTML files, Microsoft allows the use of mso-number-format to specify what Microsoft format should be used when the HTML file is opened in Excel.

I don't know whether Microsoft has a way to specify a custom number format of "M" -- I suppose it does. But here's an example that shows how to format a number with leading zeroes...using the IDVAL variable from above. The number shows as 055 and 066 in the cell, but shows as 55 and 66 in the formula bar. I know it's not exactly the same, but I'm not an Excel person, so I don't know what the equivalent Microsoft custom format (for M) would be or even if it is possible.

[pre]
ods msoffice2k file='c:\temp\test_mso2.xls';

proc tabulate data=tes;
title 'Table 2 -- Excel should show 055 and 066 for idval';
class idval ;
classlev idval / style={htmlstyle='mso-number-format:000'} ;
var values;
table idval=' ',
values;
run;
ods _all_ close;
[/pre]

cynthia
N/A
Posts: 0

Re: DUAL FORMATS after exporting to excel

We are using SAS 9.2 and it's true, they are using a custom format in excel. They want to have a value of 12M in the cell however after clicking it, Excel formula bar might have a value of 12,543,541.45 or 12,842,887.52 and so on and so forth. Message was edited by: Axe
SAS Super FREQ
Posts: 8,740

Re: DUAL FORMATS after exporting to excel

So, I'm not sure what you want. As I see it, these are your options:

1) If you use a SAS Picture format, then the number will be 12000M in the cell and 12000M in the formula bar (I interpreted your original post to say that this is undesirable behavior).

2) If you do NOT use a SAS Picture format at all, then the number will be 12000000000 in the cell AND in the formula bar

3) If you do NOT use a SAS Picture format, but, instead use HTMLSTYLE with an mso-number-format, you can get 12000M in the cell and 12000000000 in the formula bar. The mso-number-format will have to conform to a MICROSOFT format rules. The HTMLSTYLE override is just how ODS sends the format from SAS/ODS to the HTML file, so Excel knows what to do when the HTML file is opened in Excel.

It seems to me that #3 will give you the behavior that you desire. You will have to investigate within the Microsoft documentation for how to set the 12000M format using Microsoft formatting specifications.

cynthia
N/A
Posts: 0

Re: DUAL FORMATS after exporting to excel

Hi Cynthia,

True its number 3. I tried using the sample code you gave me hovever the output was not changed with the format required, can you kindly check the code. Also I would like to ask on how to apply the mso-number-format in this code:

PROC TABULATE
DATA=WORK.category_COMPUTE;
VAR SUM_of_AMT P3_AMT P6_AMT P12_cur_AMT P12_past_AMT P3_P6_AMT P3_P12_AMT MAT_AMT;
CLASS category / ORDER=DATA MISSING;
CLASS mdate / ORDER=UNFORMATTED MISSING;
CLASS YEAR / ORDER=UNFORMATTED MISSING;
TABLE
category ={LABEL="category"},

YEAR={LABEL='' STYLE={JUST=LEFT}}*
mdate={LABEL=''}*
SUM_of_AMT={LABEL=''}*F=COMMA25.2*
Sum={LABEL=''}
P3_AMT={LABEL='P3 Ave'}*
Mean={LABEL=''}*F=COMMA25.2
P6_AMT={LABEL='P6 Ave'}*
Mean={LABEL=''}*F=COMMA25.2
P12_cur_AMT={LABEL='P12 Ave Current'}*
Mean={LABEL=''}*F=COMMA25.2
P12_past_AMT={LABEL='P12 Ave Past'}*
Mean={LABEL=''}*F=COMMA25.2
P3_P6_AMT={LABEL='P3/P6'}*
Mean={LABEL=''}*F=PERCENT8.2
P3_P12_AMT={LABEL='P3/P12'}*
Mean={LABEL=''}*F=PERCENT8.2
MAT_AMT={LABEL='MAT'}*
Mean={LABEL=''}*F=PERCENT8.2 ;
;

RUN;


Tried applying it myself however it ended up with errors. I have to apply the mso-number-format on the following columns:

SUM_OF_AMT
P3_AMT
P6_AMT
P12_AMT

Thank You Very Much For Your Help since we are currently nearing our deadline.
Axe
SAS Super FREQ
Posts: 8,740

Re: DUAL FORMATS after exporting to excel

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
N/A
Posts: 0

Re: DUAL FORMATS after exporting to excel

Hi Cynthia,

Thank you for the clarifications. I discovered the custom format that they were using it was #,##0,,"M". As I followed your instruction, I saw in the stylesheet file which is a CSS file that the mso-number-format that was indicated was "\#\,\#\#0\,\,\0022M\0022".

Thank you very much for your patience and understanding
Axe
N/A
Posts: 0

Re: DUAL FORMATS after exporting to excel

Hi Cynthia,

As what I said earlier I tried following the instructions, found the real mso-number-format and applied it in a proc tabulate statement. I used ODS tagsets.msoffice2k. As I exported the file to excel and opened it, I was disappointed to see that the formats wasn't applied in excel even in the proc tabulate. Can you enlighten me?

Regards
Axe
SAS Super FREQ
Posts: 8,740

Re: DUAL FORMATS after exporting to excel

Hi:
The format will only work in the PROC TABULATE statement if and only if ALL the numbers in ALL the calculated datacells should get formatted with the custom M format. Remember that there is NO point to using your SAS format with these numbers. So a SAS format becomes irrelevant, because you are providing a Microsoft-only format.

Did you run my code????

[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;

ods msoffice2k file='c:\temp\MSO_Table.xls';
** 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;

ods _all_ close;

ods msoffice2k file='c:\temp\M_TAB_stmt.xls';
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]

If you cut and paste my code into a word processor (so that line breaks are respected) and then cut and paste again into SAS and then you run the code, I would expect you to see these results:
#1 -- both data cells -- for VALUES and for P12_CUR_AMT show the M number in the cell and the full number in the formula bar.
#2 -- again, both data cells -- for VALUES and for P12_CUR_AMT show the M number the cell and the full number in the formula bar.

When I inspect the underlying HTML with Notepad, report #1 shows these underlying HTML tags:
[pre]
<tbody>
<tr>
<th class="RowHeader" style=" text-align: left; vertical-align: top;" scope="row">aaa </th>
<td class="Data" style=" text-align: right; vertical-align: bottom;mso-number-format:\#\#\,\#\#0\,\,\0022M\0022"> 38,243,840,500.00</td>
<td class="Data" style=" text-align: right; vertical-align: bottom;mso-number-format:\#\#\,\#\#0\,\,\0022M\0022"> 12,747,946,833.33</td>
</tr>
<tr>
<th class="RowHeader" style=" text-align: left; vertical-align: top;" scope="row">bbb </th>
<td class="Data" style=" text-align: right; vertical-align: bottom;mso-number-format:\#\#\,\#\#0\,\,\0022M\0022"> 48,456,791,789.00</td>
<td class="Data" style=" text-align: right; vertical-align: bottom;mso-number-format:\#\#\,\#\#0\,\,\0022M\0022"> 16,152,263,929.67</td>
</tr>
</tbody>
[/pre]

I am using SAS 9.2:
[pre]
NOTE: SAS (r) Proprietary Software 9.2 (TS2M2)
[/pre]

If you are using SAS 9.2 and are not seeing the same results, then I can only think of a few things that might be the issue -- you are using SAS Stored Process and -think- you are using ODS MSOFFICE2K, but might in reality be using SASReport format (a SAS XML report format) that does not use the mso-number-format CSS style property. OR, you still have your SAS picture format being applied -- which will turn the number into a character string and so the mso-number-format will not be able to be applied.

If you are using SAS 9.1.3 and are not seeing the same results, then I can only conclude that there might be some difference between the MSOFFICE2K tagset template and the type of HTML that it generated. I no longer have SAS 9.1.3 to test with, however, you should be able to examine your HTML file with Notepad to determine whether/how the mso-number-format is being written to the HTML file created by ODS MSOFFICE2K.

Your best resource might be to open a track with Tech Support. They can look at ALL your code and test in both SAS 9.1.3 and SAS 9.2 to see what the problem might be.

To open a track with Tech Support, fill out the form at this link: http://support.sas.com/ctx/supportform/createForm

cynthia
N/A
Posts: 0

Re: DUAL FORMATS after exporting to excel

Hi Cythina,


I used the code that you presented to me, I added a few modifications wherein I added a new column that has a percent8.2 here is the code

data test2;
infile datalines;
input grp $ idval values pct;
p12_cur_AMT = values;
return;
datalines;
aaa 55 12000000000 .30
aaa 55 11112223333 .30
aaa 55 15131617167 .30
bbb 66 15123456234 .30
bbb 66 16111112222 .30
bbb 66 17222223333 .30
;
run;

ods msoffice2k file='C:\Documents and Settings\temp.jaabesamis\Desktop\test\MSO_Table.xls';
** 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 pct;
table grp=' ',
values*{s={htmlstyle='mso-number-format:\#\#\,\#\#0\,\,\0022M\0022'}}
pct*f=percent8.2
P12_cur_AMT={LABEL='P12 Ave Current'}*Mean*{s={htmlstyle='mso-number-format:\#\#\,\#\#0\,\,\0022M\0022'}};
keylabel sum=' '
mean=' ';
run;
ods _all_ close;

Result:

values pct P12 Ave Current
aaa 38,244M 90.00% 12,748M
bbb 48,457M 90.00% 16,152M


Everything turned out fine. Hopefully I'll be able to apply it to the real report. Thank You Very Much

Axe
Ask a Question
Discussion stats
  • 11 replies
  • 298 views
  • 0 likes
  • 2 in conversation