topic Re: Proc Report - Center all data in ODS and Base Reporting

Proc Report - Center all data

LAtwood — Fri, 16 Oct 2015 14:27:09 GMT

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;

Re: Proc Report - Center all data

ballardw — Fri, 16 Oct 2015 19:55:12 GMT

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.

Re: Proc Report - Center all data

Cynthia_sas — Fri, 16 Oct 2015 21:27:20 GMT

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

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;

Re: Proc Report - Center all data

LAtwood — Tue, 20 Oct 2015 13:08:22 GMT

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;

Re: Proc Report - Center all data

LAtwood — Tue, 20 Oct 2015 13:10:54 GMT

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?

Re: Proc Report - Center all data

Cynthia_sas — Wed, 21 Oct 2015 00:35:48 GMT

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

Re: Proc Report - Center all data

LAtwood — Thu, 22 Oct 2015 14:35:23 GMT

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;

Re: Proc Report - Center all data

LAtwood — Thu, 22 Oct 2015 14:32:21 GMT

And yes I am using this as a stored process.

Re: Proc Report - Center all data

Cynthia_sas — Fri, 23 Oct 2015 03:57:32 GMT

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

Re: Proc Report - Center all data

LAtwood — Fri, 23 Oct 2015 11:37:07 GMT

Thank you Cynthia. I have submitted a ticket.