ODS tagsets.excelxp complex formating question

[pensarchem]

Hi folks, if anyone can provide a comprehensive ODS instruction link, I can figure out myself. thanks.

SAS program also attached.

For the questions, the below are the codes;

data rpt100 ;

input CALYEAR $ paidserv2 $ counts visits ;

datalines;

2016 1 4669 4669

2016 2 2045 4090

2016 3 814 2442

2016 4 364 1456

;

run;

options nodate number orientation=landscape;

ods tagsets.ExcelXP file = "&worklib./&reportnm...xls" style = VwaExcel;

ods tagsets.ExcelXP options(embedded_titles='yes' embedded_footnotes='yes'

background='white' orientation='landscape' FITTOPAGE = 'no'

row_repeat = '9-10' autofit_height = 'yes'

frozen_headers = 'yes' center_horizontal = 'yes');

ods escapechar='^'; options missing = '';

ods tagsets.ExcelXP options(sheet_name="Test");

title1 justify=left font='Calibri' height=14pt color=white bold bcolor=darkblue "XXXXXXXXXXXXXXXXXXX";

title2 justify=left font='Calibri' italic height=12pt "FFFFFFFFFFFFFFFFFFFFFFFFFFFF";

footnote1 justify=right font='Calibri' height=10pt "SSSSSSSSSSSSSSSS";

footnote2 justify=right font='Calibri' height=10pt "LLLLLLLLLLLLLL";

ods proclabel 'Test';

proc report data=rpt100 nowd split="*";

column calyear paidserv2 counts visits;

define calyear / 'Calendar Year' STYLE(column)={TAGATTR='format:text' width=1.5in};

define paidserv2 / 'Frequency ' style(column)={width=2in};

define Counts / 'Counts ' style(column)={width=1.5in};

define Visits / 'Total ' style(column)={width=1.5in};

rbreak after / summarize style={foreground=#000000 background=#C0C0C0 font_weight=bold};

compute after;

calyear = 'Total';

endcomp;

run;

　

ods tagsets.ExcelXP close;

Please check the result in the excel file.

There are a few things I hope to change:

1, The bluepurple title background need to aligned with the width of table, regardless of the width even if I add more column;

2, There is an extra line under the title;

3, although I defined the footnote as right aligned ( in sas report, it shows me right aligned correctly), but in excel, it shows left aligned.

4, The uncovered background should be grey, which I have no idea how to define this.

5, Lastly, how to come up with the format of specific merged cell like row 19 and 20 in the excel file.

Thank you all for your help, any link or study materials will be very helpful.

[Reeza]

This paper, though a bit old is quite useful. It links each feature to it's paper reference with the proper page noted:

https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/036-2008.pdf

Links to the reference are at bottom.

[pensarchem]

Thanks Reeza. Have a good day.

[Reeza]

Sorry, one thing - are you using ODS EXCEL or TAGSETS? They are different and have different options though a lot is the same. But your subject line is ODS EXCEL and your code is TAGSETS.

[pensarchem]

it's tagsets.excelxp

I did check the link you sent me, it's helpful, but could not answer all the questions.

[SuzanneDorinski · Accepted solution]

1.  Use the MERGE_TITLE_FOOTNOTES='yes' option on the ODS tagsets.ExcelXP statement to make the title span the table.

2.  Use the SKIP_SPACE='1,0,0,1,1' option on the ODS tagsets.ExcelXP statement to get rid of the space between the title statements and the table.  The third parameter in the SKIP_SPACE option is the space after the titles. 

3.  The footnotes show up as right-aligned for me.

4.  I'm not sure what you mean by uncovered background.  Do you mean that the rest of the spreadsheet should be gray?

5.  I don't think ExcelXP can do the vertical merge in the header.  @Cynthia_sas replied to a similar question at https://communities.sas.com/t5/ODS-and-Base-Reporting/Merge-header-cells-in-report/td-p/49291

You didn't include the definition for the VwaExcel style, so I used the HTMLBlue style instead.  Changing the foreground and background colors for the Frequency, Counts, and Total columns was easy.  Changing the foreground and background colors for the spanning header Fiscalyear 20182019 requires a work-around.  

The section "COLOR IN THE HEADERS" (starting on page 18) of The REPORT Procedure and ODS Destination for Microsoft Excel: The Smarter, Faster Way to Create Firs... mentions that Excel doesn't honor background color in spanning headers.  The work-around is to create a variable from the spanning header and use it as an across variable.

%let worklib=/folders/myfolders/ODS ExcelXP;

%let reportnm=fancy_ExcelXP_output;

data rpt100 ;
   input CALYEAR $ 	paidserv2 $  counts  visits 	 ;
datalines;
2016 1 4669 4669
2016 2 2045 4090
2016 3 814  2442
2016 4 364  1456
;

options nodate number orientation=landscape;
/* ods tagsets.ExcelXP file = "&worklib./&reportnm..xls" style = VwaExcel; */

* no idea what VwaExcel style is, so used HTMLBlue instead. ;

ods tagsets.ExcelXP file = "&worklib./&reportnm..xml" style=HTMLBlue;

/* 3rd parameter in skip_space option is spacing after titles. */
/* see https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html for more details */

ods tagsets.ExcelXP options(embedded_titles='yes' embedded_footnotes='yes' 
                            background='white' orientation='landscape' FITTOPAGE = 'no'
                            row_repeat = '9-10' autofit_height = 'yes' 
                            frozen_headers = 'yes' center_horizontal = 'yes'
                            skip_space='1,0,0,1,1'
                            doc='help'
                            merge_titles_footnotes='yes');

ods escapechar='^'; options missing = '';
ods tagsets.ExcelXP options(sheet_name="Test");
    title1 justify=left font='Calibri' height=14pt color=white bold bcolor=darkblue    "XXXXXXXXXXXXXXXXXXX";
	title2 justify=left font='Calibri' italic height=12pt "FFFFFFFFFFFFFFFFFFFFFFFFFFFF";
	footnote1 justify=right font='Calibri' height=10pt "SSSSSSSSSSSSSSSS";
    footnote2 justify=right font='Calibri' height=10pt "LLLLLLLLLLLLLL";


/* https://support.sas.com/resources/papers/proceedings17/SAS0235-2017.pdf mentions that
   Excel does not honor the BACKGROUND= attribute inside spanning header - see bottom 
   of page 18 for more details.  Page 18 shows a work-around. */
  
data rpt100;
  set rpt100;
  spantext="Fiscalyear 20182019";
run;
	
ods proclabel 'Test';  
proc report data=rpt100 nowd split="*";
  column calyear  spantext,(paidserv2  counts visits);
  define calyear / 'Calendar Year'  	STYLE(column)={TAGATTR='format:text' width=1.5in}; 
  define paidserv2 	 / 'Frequency '  style(column)={width=2in} style(header)={foreground=white background=green}; 
  define Counts 	 / 'Counts ' 	style(column)={width=1.5in} style(header)={foreground=white background=green};
  define Visits 	 / 'Total ' 	style(column)={width=1.5in} style(header)={foreground=white background=green};

  define spantext / across ' ' style(header)={foreground=white background=green};
  rbreak after / summarize  style={foreground=#000000 background=#C0C0C0 font_weight=bold};
  compute after;
    calyear = 'Total';
  endcomp;
run;


ods tagsets.ExcelXP close;

ExcelXP output with complex formatting

[pensarchem]

Suzanne, Thank you very much. I truly appreciate it.

[Reeza]

Check your version is the latest as well - sometimes you need to update it, that may be the alignment issue. You can find the latest version on the SAS website. 134 I think is the latest, the version is in the log after running your tagset code.