topic Format/Style to modify gridlines in Proc Tabulate in ODS and Base Reporting

Format/Style to modify gridlines in Proc Tabulate

jhowdy — Tue, 13 Feb 2018 19:03:29 GMT

I have been tasked with outputting data processed in SAS to Word documents, to which the recipients have provided examples of how they would like the output tables to look. I have been using ods tagsets.rtf to create the Word output so far mostly with success, however I've run into a snag that has been hard to search for a solution to.

The output I need is a proc tabulate table, and the desired format is to have gridlines around all of the classes/headers, but only column gridlines for the data. The example provided does not have row gridlines for the row class or the data rows. Using a style template, I was able to produce the appropriate gridlines around the elements on top of the proc tabulate, however the row headers also have gridlines which I do not desire. I tried using style options on the class itself but this didn't work in adding nor in removing gridlines.

Attached is a screengrab with boxes highlighting the gridlines I would like to remove, and my code is below. For a bonus, if anyone knows if I can get "State" in the screengrab centered at the bottom of the box instead of full center that would help as well!

This is my template for the style, followed by the ods and proc tabulate snippet: :

proc template;
  define style Styles.smaller;
    parent = styles.rtf;
    style Table from output / borderspacing=0 Rules=cols;
    style header from headersandfooters / background=white borderbottomwidth=1pt;
  end;
run;

ods tagsets.rtf file="&something.doc"
                style=styles.smaller startpage=now;

proc tabulate data=ppwfps2;
  class state;
  class a b c / DESCENDING;
  var v;
  table state=' ', (a=' ')*(b=' ')*(c=' ')*v=' '
        / BOX='State';
  keylabel sum = ' '; *Remove sum label from table.;
run;

Re: Format/Style to modify gridlines in Proc Tabulate

Cynthia_sas — Tue, 13 Feb 2018 20:12:54 GMT

Hi:

When I run a version of your code that uses SASHELP.PRDSALE, this is what I get:

I am running SAS 9.4 M3 version. To me, this looks very like what you posted.

cynthia

Re: Format/Style to modify gridlines in Proc Tabulate

jhowdy — Tue, 13 Feb 2018 20:42:02 GMT

That looks exactly like what I'm getting, but I would like to remove the horizontal gridlines between the country names in that example. After some more digging, I found I could remove these using classlev / style ={borderbottomwidth=0pt}, however the very last row in the table doesn't have a line so there is a gap in the outer border of the whole table just underneath the final row of data. Not the most elegant solution but it's close!

(Right now it looks similar to the example you posted, however the horizontal lines are gone beneath the cells containing the country names. I would like the one underneath U.S.A. that is removed back, there's a hole in the table!)

Re: Format/Style to modify gridlines in Proc Tabulate

ballardw — Tue, 13 Feb 2018 20:57:04 GMT

Without data can't get better but you can use a format to adjust classlev appearance options:

proc format library=work;
value cbottom
'U.S.A' = 2pt
other   = 0pt
;
run;

/* in tabulate*/
classlev country /style=(borderbottomwidth=cbottom.);

Obviously this may involve trial an error with your data as you did not provide any examples of your variable types or values.

Re: Format/Style to modify gridlines in Proc Tabulate

jhowdy — Tue, 13 Feb 2018 21:24:21 GMT

Thank you! This did the trick after specifying the character format $cbottom.

I apologize for the lack of example, I had this screenshot attached as a pdf. Just noticed the photo button.