The SAS Output Delivery System and reporting techniques

In an xml spreadsheet, how can I give unused cells a transparent background?

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

In an xml spreadsheet, how can I give unused cells a transparent background?

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;


Accepted Solutions
Solution
‎10-18-2012 02:10 AM
SAS Super FREQ
Posts: 8,862

Re: In an xml spreadsheet, how can I give unused cells a transparent background?

Posted in reply to AdrianGriffin

Hi:

  Look at this Tech Support note. It says that using background=transparent doesn't work with TAGSETS.EXCELXP and the solution illustrates a different method.

http://support.sas.com/kb/46/949.html

cynthia

View solution in original post


All Replies
Solution
‎10-18-2012 02:10 AM
SAS Super FREQ
Posts: 8,862

Re: In an xml spreadsheet, how can I give unused cells a transparent background?

Posted in reply to AdrianGriffin

Hi:

  Look at this Tech Support note. It says that using background=transparent doesn't work with TAGSETS.EXCELXP and the solution illustrates a different method.

http://support.sas.com/kb/46/949.html

cynthia

Contributor
Posts: 32

Re: In an xml spreadsheet, how can I give unused cells a transparent background?

Posted in reply to Cynthia_sas

Thanks, Cynthia. I tried it today and it works like a charm.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 305 views
  • 0 likes
  • 2 in conversation