The SAS Output Delivery System and reporting techniques

Workaround for Excel Justification?

Reply
Super Contributor
Posts: 268

Workaround for Excel Justification?

Hello,
I have a fairly straighforward Proc Report sending output to ODS Excel. Some of the columns have both character and numeric data. As expected Excel right justifies the numeric data (and also strips the leading zeroes) and left justifies all the character data for that column.

When I see the same output in PDF, the same column is actually centered.

My questions are -

1. Is there a way to left align the entire column in both ODS Excel and PDF. My code is here -
2. There is a style=MSO option which will keep the leading zeroes. Can anyone give the exact option I need use?

PROC REPORT DATA=PT_PCT nowindows;
COLUMN ('Plan Type' PLAN_TYPE);
DEFINE PLAN_TYPE / ' ' DISPLAY ;
run;

Thank you,
saspert I found this post in one of the earlier threads. Still not getting the results. Only making it worse by applying the HTML style to that column.

style(column)={htmlstyle="text-align:left;" }

Message was edited by: saspert
SAS Super FREQ
Posts: 8,743

Re: Workaround for Excel Justification?

Hi:
There is an ODS PDF destination but not an "ODS EXCEL" destination. The ODS HTML destination, the ODS MSOFFICE2K destination and the ODS MSOFFICE2K_X destination all create HTML files that Excel knows how to open and render. Since ODS MSOFFICE2K and ODS TAGSETS.MSOFFICE2K_X both create Microsoft specific HTML tags, I would recommend using them instead of ODS HTML.

There is another destination called ODS TAGSETS.EXCELXP. As I said, as far as I know, there is no "ODS EXCEL" destination. Because you referred to HTMLSTYLE, that implies you are using HTML-based methods to create an HTML ASCII text file that Excel can open and render. However, without knowing EXACTLY what destination you are interested in, it is very hard to provide suggestions.

However, when I run the code below, the ODS HTML type of output does NOT use the JUST=LEFT. This does not surprise me, since ODS HTML creates W3C compliant HTML -- and NOT Microsoft compliant HTML. When I open the "Microsoft flavor" of HTML with Excel, the left justification is respected. Also, the HTMLSTYLE for leading zeroes is respected. I do NOT expect MSO-NUMBER-FORMAT to ever work with W3C flavor of HTML. PDF will not respect or use MSO-NUMBER-FORMAT -- but PDF shows the leading zeroes and does respect the simple just=l in the STYLE= override syntax.

cynthia
[pre]
data class;
set sashelp.class(obs=3);
retain x 111111;
output;
name=put(x,z8.);
output;
x + 1;
run;

ods pdf file='c:\temp\pdf_out.pdf';
ods html file='c:\temp\ht4_w3c.xls' style=sasweb;
ods msoffice2k file='c:\temp\ht_mso.xls' style=sasweb;
ods tagsets.msoffice2k_x file='c:\temp\ht_msox.xls' style=sasweb;
proc report data=class nowd;
column name age height weight;
define name / display
style(column)={just=l cellwidth=1.5in htmlstyle='mso-number-format:00000000'};
run;
ods _all_ close;
[/pre]
Super Contributor
Posts: 268

Re: Workaround for Excel Justification?

Thank you Cynthia. Sorry about the choice of words. What I meant was Excel Output using ODS HTML (and not necessarily ODS Excel).

Appreciate your explanation about the working of these destinations.
Super Contributor
Posts: 268

Re: Workaround for Excel Justification?

Hi Cynthia,
When I try your code, the formatting looks ok but I get a grey background for the table when I use a sasweb style. Do you get the same? Is there a way to turn it off?

Thanks,
saspert
SAS Super FREQ
Posts: 8,743

Re: Workaround for Excel Justification?

Hmmm. When I run the code, using MSOFFICE2K and STYLE=SASWEB, the report table has a white background. The area -outside- the table is gray. This is not true of every style -- just selected styles, like SASWEB and EGDEFAULT, to name just a few.

Without changing the style template, the thing that you can do is switch to a different style in the STYLE= option. When I use the following templates, only SASWEB and EGDEFAULT show a gray background under the cells to the right of the actual ODS output table.

cynthia
[pre]

ods msoffice2k(1) file='c:\temp\ht_mso1.xls' style=sasweb;
ods msoffice2k(2) file='c:\temp\ht_mso2.xls' style=minimal;
ods msoffice2k(3) file='c:\temp\ht_mso3.xls' style=analysis;
ods msoffice2k(4) file='c:\temp\ht_mso4.xls' style=journal;
ods msoffice2k(5) file='c:\temp\ht_mso5.xls' style=statdoc;
ods msoffice2k(6) file='c:\temp\ht_mso6.xls' style=barrettsblue;
ods msoffice2k(7) file='c:\temp\ht_mso7.xls' style=ocean;
ods msoffice2k(8) file='c:\temp\ht_mso8.xls' style=watercolor;
ods msoffice2k(9) file='c:\temp\ht_mso9.xls' style=harvest;
ods msoffice2k(10) file='c:\temp\ht_mso10.xls' style=egdefault;

proc report data=class nowd;
column name age height weight;
define name / display
style(column)={just=l cellwidth=1.5in htmlstyle='mso-number-format:00000000'};
run;
ods _all_ close;
[/pre]
Ask a Question
Discussion stats
  • 4 replies
  • 552 views
  • 0 likes
  • 2 in conversation