BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
3 REPLIES 3
Eric_SAS
SAS Employee
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;
deleted_user
Not applicable
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?
deleted_user
Not applicable
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.

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