BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9
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
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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]
saspert
Pyrite | Level 9
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.
saspert
Pyrite | Level 9
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
Cynthia_sas
SAS Super FREQ
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1334 views
  • 0 likes
  • 2 in conversation