topic Re: How do I hide PROC REPORT Column Borders in ODS PDF? in ODS and Base Reporting

How do I hide PROC REPORT Column Borders in ODS PDF?

EricS — Tue, 30 Apr 2019 03:11:22 GMT

Hi, I've searched for an answer to this, but have come up blank. I'm using SAS 9.4 Level 1M5 in a Window server environment.

Problem: I'm trying to create production-quality tables using PROC REPORT in an ODS PDF output where I can group columns together having borders between certain columns, but not other columns. I'm using the column and header styles to do adjust the appearances. The only partial solution I've found is setting the vertical border colors to WHITE, but this creates gaps in the horizontal lines; this is close but looks bad for what I'm doing. I've tried setting border widths to zero, but that doesn't seem to help.

Question: Is there a way to make borders in PROC REPORT clear or transparent or non-existent? Or to change the order in which they are drawn? I was envisioning an option that sets the border/line transparency to 100%, but I don't see that option.

An example of what I'm trying to do is generated by the code below -- you'll see that there are gaps in the horizontal lines between the columns that I set to WHITE. In the example, I set the left-side border to grey (borderleftcolor=grey), and columns where I'd like to have no border to white (borderleftcolor=white). The sample code also sets the left border widths to zero for the ones I want to be blank (borderleftwidth=0pt), but this does not appear to have an effect (I read that this works best in ODS RTF).

Any ideas would be greatly appreciated! Thanks in advance!

*Data setup;
data baseball;
  set sashelp.baseball;
  nBA=nHits/nAtBat;
  CrBA=CrHits/CrAtBat;
  format nBA CrBA 8.3;
  label
	nBA="Batting Average in 1986"
	CrBA="Career Batting Average";
  ;
run;

proc sort data=baseball;
  by descending nBA;
run;

*Top 20 MLB batting averages;
data baseball (where=(rank<=20));
  set baseball;
  Rank=_n_;
run;

*Output file setup;
%let path=C:\;
ods pdf file="&path.baseball_ba.pdf";

options orientation=landscape;

title "1986 MLB Batting Average Leaders";
footnote;

proc report data=baseball nofs; 
  column ("Player Info" Rank Name Team league) 
	 ("1986 Stats" nAtBat nHits nBA) 
	 ("Career Stats" CrAtBat CrHits CrBA);
   
  *First column in each grouping has a left border that is grey, with subsequent columns having white left borders;
  define Rank / display id style(column)={borderleftcolor=grey} style(header)={borderleftcolor=grey};
  define Name / display id style(column)={borderleftwidth=0pt borderleftcolor=white} style(header)={borderleftwidth=0pt borderleftcolor=white};
  define Team / display id style(column)={borderleftwidth=0pt borderleftcolor=white} style(header)={borderleftwidth=0pt borderleftcolor=white};
  define League / display id style(column)={borderleftwidth=0pt borderleftcolor=white} style(header)={borderleftwidth=0pt borderleftcolor=white};

  define nAtBat / display style(column)={borderleftcolor=grey} style(header)={borderleftcolor=grey}; 
  define nHits / display style(column)={borderleftwidth=0pt borderleftcolor=white} style(header)={borderleftwidth=0pt borderleftcolor=white};  
  define nBA / display style(column)={borderleftwidth=0pt borderleftcolor=white} style(header)={borderleftwidth=0pt borderleftcolor=white};  

  define CrAtBat / display style(column)={borderleftcolor=grey} style(header)={borderleftcolor=grey}; 
  define CrHits / display style(column)={borderleftwidth=0pt borderleftcolor=white} style(header)={borderleftwidth=0pt borderleftcolor=white};  
  define CrBA / display style(column)={borderleftwidth=0pt borderleftcolor=white borderrightcolor=grey} style(header)={borderleftwidth=0pt borderleftcolor=white borderrightcolor=grey};

run;

ods pdf close;

Re: How do I hide PROC REPORT Column Borders in ODS PDF?

Norman21 — Tue, 30 Apr 2019 05:00:51 GMT

Interesting question.

You could try the technique "OVERLAYING GRAPHIC OUTPUT ON TABULAR DATA", described on page nine of this document:

https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/092-31.pdf

Re: How do I hide PROC REPORT Column Borders in ODS PDF?

EricS — Tue, 30 Apr 2019 05:41:52 GMT

Thanks, Norman21. That's an interesting strategy, which should work for smaller/single-page tables. I'll have to think about how this would work for larger tables spanning across pages.

Re: How do I hide PROC REPORT Column Borders in ODS PDF?

ballardw — Tue, 30 Apr 2019 15:00:20 GMT

First thing I would try would be to use a style such a JOURNAL which typically does not put vertical borders and then overwrite where I wanted a border. Such as:

ods pdf file="&path.baseball_ba.pdf" style=journal;
options orientation=landscape;
title "1986 MLB Batting Average Leaders";
footnote;
proc report data=baseball nofs; 
  column ("Player Info" Rank Name Team league) 
	 ("1986 Stats" nAtBat nHits nBA) 
	 ("Career Stats" CrAtBat CrHits CrBA);
     *First column in each grouping has a left border that is grey, with subsequent columns having white left borders;
  define Rank / display id style(column)={borderleftcolor=grey } style(header)={borderleftcolor=grey };
  define Name / display id style(column)={  } style(header)={borderleftwidth=0pt  };
  define Team / display id style(column)={  } style(header)={borderleftwidth=0pt  };
  define League / display id style(column)={   } style(header)={   };

  define nAtBat / display style(column)={borderleftcolor=grey} style(header)={borderleftcolor=grey}; 
  define nHits / display style(column)={   } style(header)={   };  
  define nBA / display style(column)={   } style(header)={  };  

  define CrAtBat / display style(column)={borderleftcolor=grey   } style(header)={borderleftcolor=grey  }; 
  define CrHits / display style(column)={   } style(header)={   };  
  define CrBA / display style(column)={  borderrightcolor=grey} style(header)={  borderrightcolor=grey};

run;

ods pdf close;

may get you started. Need to work on the header yet though.

Typically anywhere you have border between columns you would set both the right and left width to 0 (unit not needed in this case)

Re: How do I hide PROC REPORT Column Borders in ODS PDF?

BrunoMueller — Tue, 30 Apr 2019 15:43:20 GMT

I would also use a journal based style and then add the elements as need, for the spanning headers you can use inline formatting.

Add this code as second step after your original Proc REPORT and have a look. It will change the style used for this second output

ods escapechar="^";
ods pdf style=journal;
proc report data=baseball nofs; 
  column ("^{style [borderbottomcolor=black borderrightcolor=black]Player Info}" Rank Name Team league) 
	 ("^{style [borderbottomcolor=black borderrightcolor=black]1986 Stats}" nAtBat nHits nBA) 
	 ("^{style [borderbottomcolor=black]Career Stats}" CrAtBat CrHits CrBA);
     *First column in each grouping has a left border that is grey, with subsequent columns having white left borders;
  define Rank / display id style(column)={ } style(header)={ };
  define Name / display id style(column)={  } style(header)={  };
  define Team / display id style(column)={  } style(header)={  };
  define League / display id style(column)={ BORDERrightCOLOR=black  } style(header)={ BORDERrightCOLOR=black  };

  define nAtBat / display style(column)={} style(header)={}; 
  define nHits / display style(column)={   } style(header)={   };  
  define nBA / display style(column)={ borderrightcolor=black  } style(header)={ borderrightcolor=black };  

  define CrAtBat / display style(column)={  } style(header)={ }; 
  define CrHits / display style(column)={   } style(header)={   };  
  define CrBA / display style(column)={  } style(header)={  };

run;

Re: How do I hide PROC REPORT Column Borders in ODS PDF?

EricS — Tue, 30 Apr 2019 19:54:44 GMT

Thank you, ballardw -- this is great. Much appreciated.

Re: How do I hide PROC REPORT Column Borders in ODS PDF?

EricS — Tue, 30 Apr 2019 19:57:24 GMT

Thanks, Bruno_SAS. That's a great idea to use inline formatting.