topic Re: Proc Print to Excel does not split labels in ODS and Base Reporting

Proc Print to Excel does not split labels

deleted_user — Tue, 09 Sep 2008 11:59:42 GMT

Hi there,

using following code I get no splitted labels in the headers of the excel-sheet:

TITLE1 f=arial h=10pt j=l bold "some title";
TITLE2 f=arial h=8pt j=l bold "some title" ;
TITLE3 f=arial h=12pt j=l bold 'other subtitle' ;
TITLE4 f=arial h=10pt j=l bold 'other subtitle' ;
options ls=256;

ods listing close;
ods tagsets.excelxp
file="C:\myfile.xls"
style=mystyle /* contains font specs only */
options( sheet_interval='bygroup'
sheet_label=' '
autofilter='all'
frozen_headers='7'
embedded_titles='yes'
embedded_footnotes='yes'
autofit_height = 'yes');
run;

proc print data=work.test noobs label split='+'
style( header ) = {background=CX800000 foreground=CXFFFFFF} ;
by bvar;
sum v1 / style={tagattr='format:#,##0'};
... more sum statements as above ...
pageby bvar;
var bvar / style={tagattr='format:@'};
var v1 / style={tagattr='format:#,##0'};
.... more vars as above ...
label
v1='firstline+secondline'
... more labels statements...
;
run;

I am using SAS 9.1.3 SP4 under WinXP SP2, using ExcelXP tagset v1.86, 04/15/08.
Whatsoever I do, I don't get any split headers in the xcl-sheet, only the sign for the non-printable char. When I edit the cell, excel show the right formatting (two lines), which are applied when I leave the cell with , but that's no use in production...

Any ideas?

Re: Proc Print to Excel does not split labels

Eric_SAS — Wed, 10 Sep 2008 20:03:41 GMT

I am not sure why your example does not work. I used the following example with good results.

/*-- Create data --*/
/*-----------------*/
data prdsale;
set sashelp.prdsale;
Difference = actual-predict;
run;

proc sort data=prdsale;
by country region division year;
run;

/*-- Create ExcelXP output using modified --*/
/*-- style. --*/
/*-- Using autofilter, frozen_headers, --*/
/*-- frozen_rowheaders, auto_subtotals --*/
/*------------------------------------------*/
%filename (a, mexl18ab, xml);

ods tagsets.excelxp file=a options(autofilter='1-3' frozen_headers='2'
frozen_rowheaders='4' auto_subtotals='yes');

/*-- Use Excel formulas to represent computed cells, --*/
/*-- and use an Excel format to force Excel to show --*/
/*-- negative currency values in red and with the --*/
/*-- format ($nnn). In the formula below, the RC --*/
/*-- value corresponds to the cell relative to the --*/
/*-- current cell. For example, RC[-2] means "2 --*/
/*-- cells to the left of the current cell". Any --*/
/*-- valid Excel formula can be used, and the formula --*/
/*-- used here matches the computation performed --*/
/*-- in the DATA step that created the column. --*/
/*------------------------------------------------------*/

title2 'Print of data using tagattr with formats';
title3 'predict & actual - ';
title4 'difference - ';
title5 'Sums - ';
proc print data=prdsale noobs label;
id country region division;
var prodtype product quarter month year;
var predict actual / style={tagattr='format:$#,##0_);[Red]$$#,##0$'};
var difference /
style={tagattr='format:$#,##0_);[Red]$$#,##0$ formula:RC[-1]-RC[-2]'};
sum predict actual difference /
style={tagattr='format:$#,##0_);[Red]$$#,##0$'};;
run;

title6 'Adding labels to prodtype, predict and actual';
proc print data=prdsale noobs label split='*';
id country region division;
var prodtype product quarter month year;
var predict actual / style={tagattr='format:$#,##0_);[Red]$$#,##0$'};
var difference /
style={tagattr='format:$#,##0_);[Red]$$#,##0$ formula:RC[-1]-RC[-2]'};
sum predict actual difference /
style={tagattr='format:$#,##0_);[Red]$$#,##0$'};;
label prodtype = 'Product*Type'
predict = 'Predicted*Sales*For Area'
actual = 'Actual*Sales*Amount';
run;

ods tagsets.excelxp close;

Re: Proc Print to Excel does not split labels

deleted_user — Thu, 11 Sep 2008 07:29:52 GMT

Hello Eric,

thank you for your instructive reply.

Running your example I have found a possible reason for the non-split headers:

When I add to the second PROC PRINT statement
the option
STYLE( HEADER ) = {BACKGROUND=CX800000 FOREGROUND=CXFFFFFF}

the split is no longer in effect when I open the result in excel.
I have to remove the style option and figure out how to color my headers using the style={tagattr= statement on each var.
Is this possible?

Re: Proc Print to Excel does not split labels

deleted_user — Tue, 16 Sep 2008 10:51:35 GMT

Hi,

I have removed the STYLE(HEADER)= statement fomr the invocation of proc print and added to the SUM and VAR statements the STYLE(HEADER)={...} option individually, and voilá: it works.