If the background is transparent, the screen gridlines will be visible — I think this makes the spreadsheet easier to navigate and reassures the user that there is nothing missing.
But I can’t find a style element that controls unoccupied cells?
Any ideas?
Here is some code that does most of what I want. But when I open the sheet in Excel 2010, the unused cells have a whit-out background, so screen gridlines are not visible.
*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;
*Generate some data;
*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;
data Sales;
do year=2008 to 2012;
do Salesman='Smith ', 'Jones', 'Valdez', 'Lee';
do County='Alameda', 'Sonoma', 'Yolo', 'Lake', 'Merced';
do Industry='Wineries', 'Packers';
do Product='Pump ', 'Hoist', 'Furnace', 'Fan';
Sales=round(10000+ranuni(88)*10000, 10);
Mileage=round(800+ranuni(88)*80);
if ranuni(3)<0.8 then output;
end;
end;
end;
end;
end;
attrib mileage format=comma11. label='Mileage reimbursed';
attrib sales format=comma11. label='Annual sales';
run;
options pageno=1;
proc sort data=sales; by salesman; run;
*ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ;
*Old-school listing table;
proc tabulate noseps data=sales f=comma8.;
class year salesman county product industry; var sales mileage;
table salesman=' ', year=' '*county=' ', sales*sum=' '*Industry=' '*product=' '
/indent=2 rts=16;
title2'Salesman performance trends';
Footnote 'Not for distribution';
run;
*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;
*XML output suitable for Excel using tagsets.excel
*””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””;
proc template;
*Style suitable for the plain look that I want;
define style styles.excel_xml_test;
parent=styles.minimal;
style pages/background=transparent;
style document/background=transparent;
style body/background=transparent topmargin=0.5in bottommargin=0.5in leftmargin=0.5in rightmargin=0.5in;
style systemtitle/font_face='Gill Sans MT' font_size=12pt verticalalign=middle;
style systemfooter/font_face='Gill Sans MT' font_size=11pt just=left verticalalign=middle;
style caption/background=transparent cellheight=24pt;
style pageno/font_face='Gill Sans MT' font_size=11pt;
style byline/font_face='Gill Sans MT' font_size=11pt;
style table/font_face='Gill Sans MT' font_size=11pt ;
style header/verticalalign=middle cellheight=24pt bordertopwidth=1pt borderbottomwidth=1pt;
style rowheader/verticalalign=top borderbottomwidth=0pt bordertopwidth=0pt;
style parskip/cellheight=21pt background=transparent just=left;
end;
run;
ods tagsets.excelxp file="&desktop.xmltest.xml" style=excel_xml_test
options(embedded_titles='yes' embedded_footnotes='yes' suppress_bylines='yes'
skip_space='0, 0, 1, 0, 0' row_heights='21, , , , , , 21'
sheet_label=' ' sheet_interval='bygroup'
width_fudge='0.6');
options pageno=1;
proc report nofs headline data=sales;
column (salesman year county sales, industry, product);
define salesman/ group noprint;
define year/group ' ' left;
define county/group ' ';
define industry/across ' ';
define product/across ' ';
define sales/style=[TAGATTR='format:#,##0'];
run;
proc tabulate noseps data=sales f=comma8. style=[TAGATTR='format:#,##0'];
class year salesman county product industry; var sales mileage;
table salesman=' ', year=' '*county=' ', sales*sum=' '*Industry=' '*product=' '
/indent=2 rts=16;
title2'Salesman performance trends';
Footnote 'Not for distribution';
footnote2 'Second footnote line';
by salesman;
run;
ods tagsets.ExcelXP close;
