BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AdrianGriffin
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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

AdrianGriffin
Obsidian | Level 7

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

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