BookmarkSubscribeRSS Feed
LAtwood
Calcite | Level 5
When I run the below code it is opening in Excel.  The "# of coils" data is left justified in excel.  
I want it to be center justified. Any help would be appreciated.

proc report data=WORK.APPEND_TABLE_0038 nowd STYLE(REPORT)={rules=all cellspacing=0 bordercolor=black} ; column color no_of_coils ; TITLE1 "<td align=left colspan=5 ><font size=4 color=black><b>Rust Prevention Report: %TRIM(%QSYSFUNC(DATE(), NLDATE20.))</b></font></td>"; TITLE2 "<td align=center colspan=5 ><font size=3 color=black><b>TPC Slit Coils</b></font></td>"; TITLE3 "<td align=center colspan=5 ><font size=2 color=black><b>6-10 Yellow; More than 10 Red</b></font></td>"; compute no_of_coils; if color = 'yellow' then call define ('no_of_coils','style','style=[background=yellow]'); if color = 'red' then call define ('no_of_coils','style','style=[background=red]'); endcomp; define no_of_coils / display '# of coils'; define color / display noprint; ; run;
9 REPLIES 9
ballardw
Super User

Please show the code that sends this to Excel, ODS EXCEL, TAGSETS.EXCELXP, MSOffice, html being opened with Excel, something else. The different methods of sending to Excel sometimes require different approaches to get the desired appearance.

Cynthia_sas
SAS Super FREQ

Hi:

  Some sample code with SASHELP.CLASS illustrates how to do centering. I did each column as centered and varied the headers so you could see the impact. When I use ODS EXCEL or TAGSETS.EXCELXP, the results are the same...

 

cynthia

centering_ods_excel.png

Here's the code:

ods excel file='c:\temp\o_x_new.xlsx' style=htmlblue;
ods tagsets.excelxp file='c:\temp\o_t_xp.xml' style=htmlblue;

proc report data=sashelp.class;
  column name age sex;
  define name /
     style(header)={just=left cellwidth=1.0in}
     style(column)={just=center cellwidth=1.0in};
  define age /
     style(header)={just=right cellwidth=1.0in}
     style(column)={just=center cellwidth=1.0in};
  define sex /
     style(header)={just=center cellwidth=1.0in}
     style(column)={just=center cellwidth=1.0in};
run;
ods _all_ close;

LAtwood
Calcite | Level 5

GOPTIONS ACCESSIBLE;
%LET RV=%SYSFUNC(APPSRV_HEADER(CONTENT-TYPE,APPLICATION/VND.MS-EXCEL)));
ODS PATH work.templat(update) WORK.templat(read) sashelp.tmplmst(read);

PROC TEMPLATE;
DEFINE STYLE STYLES.TEST;
STYLE TABLE /
borderwidth=1
bordercolor=black
font_face=black;
END;
RUN;

ODS LISTING CLOSE;

ODS HTML BODY=_WEBOUT STYLE=STYLES.TEST;

LAtwood
Calcite | Level 5

Also, I probably wasn't clear enough.  The whole column needs to be centered on the page.  Currently when opening the one column displays in column A in the spreadsheet (all the way to the left).  Is there a way to center this one column on the page?

Cynthia_sas
SAS Super FREQ
Hi:
You're using ODS HTML to _WEBOUT with APPSRV_HEADER -- are you using SAS/IntrNet or SAS Stored Processes?

Sorry I misunderstood what you meant. With HTML output, even if you "fool" the browser into launching Excel for you with your APPSRV_HEADER, Excel's default treatment for HTML files is to start the output in Column A. That is not what I consider "left-justifying" the column. HTML output when opened in Excel is still HTML output treated like an Excel sheet. The "centering" that can happen is an after the fact control that you add when you provide the Print instructions and tell Excel to center the output horizontally and vertically on the page.

Or, instead of ODS HTML (depending on your version of SAS), you might consider using a different destination that allows more control over the PRINT options when ODS output is opened in Excel. I believe that TAGSETS.EXCELXP and TAGSETS.MSOFFICE2K_X both allow you to specify printing options in the syntax. But with plain ODS HTML, the only place the output ever starts is in Column A on the worksheet.

That is the way that Excel works with HTML.

Now, if you are using this as a SAS Stored Process and the SAS Add-in for Microsoft Office, then when you run the Stored Process, you get a prompt asking you where you want the output to be placed in the existing sheet, in a new sheet or in a new workbook. My memory is that if you place the output in the existing sheet or a new sheet, you get to give the starting column, such as A1 or G2, etc.

cynthia
LAtwood
Calcite | Level 5

Hi Cynthia,

 

I downloaded excelxp tagset.  I now have the following code.

Is it possible to center the column "# of coils" to the center of the excel spreadsheet?  I cannot select a column like you suggested above because I have other proc reports that will display on the same sheet with more columns which will then cause this first proc report to still look aligned to the left.

Also, my title does not display because it only displays to the width of the column "# of coils."  If I make the cellwidth larger of the column I can get the full title to display.  Any advice on this also?

 

GOPTIONS ACCESSIBLE;
%LET RV=%SYSFUNC(APPSRV_HEADER(CONTENT-TYPE,APPLICATION/VND.MS-EXCEL));
%LET RV=%SYSFUNC(APPSRV_HEADER(CONTENT-DISPOSITION,ATTACHMENT; FILENAME="RUST_RPT_%sysfunc(today(),MMDDYY8.).XLS"));
ODS PATH work.templat(update) sasuser.templat(read) sashelp.tmplmst(read);
ods listing close;

 

ODS tagsets.excelxp file=_WEBOUT style=styles.journal_borders
options(sheet_interval='none' sheet_name='Rust'
embedded_titles='yes' orientation='portrait' suppress_bylines='yes');

 

proc report data=WORK.APPEND_TABLE_0038 nowd;
column color no_of_coils ;
TITLE1 f='Calibri' h=12pt "Rust Prevention Report: %TRIM(%QSYSFUNC(DATE(), NLDATE20.))";
TITLE2 f='Calibri' h=11pt "TPC Slit Coils";
TITLE3 f='Calibri' h=10pt "6-10 days: Yellow > 10 days: Red";

compute no_of_coils;

if color = 'yellow'
then
call define ('no_of_coils','style','style=[background=yellow]');
if color = 'red'
then
call define ('no_of_coils','style','style=[background=red]');

endcomp;


define no_of_coils / display '# of coils';
define color / display noprint;


;
run;

 

LAtwood
Calcite | Level 5
And yes I am using this as a stored process.
Cynthia_sas
SAS Super FREQ
Hi: So, what do you define as the "center" of the Excel spreadsheet. You only have 1 column on the report. By default, the output starts in Column A.

When you run this as a stored process, if the client application is Excel, you get prompted for the location to place the stored process output, when you use one of the supported destinations. This is a pop-up window inside the client application, so it cannot be controlled by SAS.

And, even with TAGSETS.EXCELXP, if you use the CENTER_HORIZONTAL suboption, it still puts the column in A, but when printing centers the column on the print preview page.

So I am not sure you can control the report the way you want or envision. This would be a question for Tech Support.
cynthia
LAtwood
Calcite | Level 5

Thank you Cynthia.  I have submitted a ticket.

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
  • 9 replies
  • 4549 views
  • 0 likes
  • 3 in conversation