topic Re: How to control the location of horizontal and vertical lines of a table generated by proc report ... in ODS and Base Reporting

How to control the location of horizontal and vertical lines of a table generated by proc report ...

jzhang332002 — Fri, 14 Nov 2014 20:35:58 GMT

Hi,

I want to control the location of horizontal and vertical lines of a table generate by proc report. For example, I have column x1(group), x2(group), x3, x4, x5, x6. The data structure is x1 * x2 * (x3 x4 x5 x6). I only want horizontal lines appears after x1, and vertical lines appears after x2, x4. The setting of my style is "frame=void". Any suggestion?

Thanks.

Jian

Re: How to control the location of horizontal and vertical lines of a table generated by proc report ...

ballardw — Fri, 14 Nov 2014 21:16:30 GMT

I would start by using an ODS style without many lines to begin with such as Journal. Then add style overrides for borderbottomwidth and borderbottomcolor for x1 and borderrightwidth / color for X2 and X4.

Re: How to control the location of horizontal and vertical lines of a table generated by proc report ...

Cynthia_sas — Sat, 15 Nov 2014 05:22:19 GMT

Hi, just giving us partial code and saying that you are using frame=void is not much help. It would be useful to see ALL your code, not just a description of your columns, useful to see the style that you're starting with, because FRAME=VOID will OVERRIDE the existing style settings. But, the FRAME attribute usually has an interaction with the RULES attribute and the BORDERSPACING/CELLSPACING attributes, so seeing more of your code would be useful. It should be fairly easy for you to mock up a sample program using some of the SASHELP datasets.

In addition, you do not say what destination you are interested in. Different destinations render the interior table lines and the box (frame) around the box differently. So while BallardW's suggestion is a good one (to start with JOURNAL), there are destinations that completely ignore the JOURNAL settings for interior table lines (such as ODS MSOFFICE2K or ODS TAGSETS.EXCELXP).

For example, consider the code below -- starting with JOURNAL style, note how there are slight differences in Report #2 in the different "primary" destinations RTF, PDF and HTML, but the HTML opened with Excel and the TAGSETS.EXCELXP output completely ignore the style overrides in the #2 report.

It is OK for you to ask how rules/frame work, but you haven't provide full information about the destination you are ultimately interested in. That information is critical to the correct answer. Without knowing WHAT your final destination is or what code you've tried or what style you are starting from, everybody is just guessing with incomplete information.

Cynthia

ods rtf file='c:\temp\testframe.rtf' style=styles.journal;

ods pdf file='c:\temp\testframe.pdf' style=styles.journal;

ods html file='c:\temp\testframe_ht.html' style=styles.journal;

ods msoffice2k(id=1) file='c:\temp\testframe_mso1.html' style=styles=journal;

ods msoffice2k(id=2) file='c:\temp\testframe_mso2.xls' style=styles.journal;

ods tagsets.excelxp file='c:\temp\testframe_xp.xml' style=styles.journal;

title '1) Default Behavior no overrides';

proc report data=sashelp.shoes nowd spanrows;

where region in ('Asia', 'Canada');

column region product sales inventory returns;

define region / group;

define product / group;

define sales / sum;

define inventory / sum;

define returns / sum;

rbreak after /summarize;

run;

title '2) Example from Forum Question';

proc report data=sashelp.shoes nowd

Style(report)={frame=void rules=rows cellspacing=10 background=cyan bordercolor=black};

where region in ('Asia', 'Canada');

column region product sales inventory returns;

define region / group;

define product / group;

define sales / sum;

define inventory / sum;

define returns / sum;

rbreak after /summarize;

run;

title '3) Example with cellspacing=0 background color disappears';

proc report data=sashelp.shoes nowd

Style(report)={frame=void rules=rows cellspacing=0 background=cyan bordercolor=black};

where region in ('Asia', 'Canada');

column region product sales inventory returns;

define region / group;

define product / group;

define sales / sum;

define inventory / sum;

define returns / sum;

rbreak after /summarize;

run;

ods _all_ close;

Re: How to control the location of horizontal and vertical lines of a table generated by proc report ...

jzhang332002 — Mon, 17 Nov 2014 19:58:23 GMT

My code is:

ODS listing close;

ODS PDF FILE = "Report.pdf" Style = Styles.journal;

ODS escapechar='~';

options orientation = landscape NoDate NoNumber;

title;

proc report data = Report&Num.b nowd spanrows

Style(report)={font_size=0.3 font_face=arial background=white just=center frame=void rules=groups cellpadding=1pt cellspacing=1pt height=0.2in}

Style(header)={font_size=0.3 font_face=arial background=white just=center}

Style(column)={font_size=0.3 font_face=arial background=white just=center};

column CPN VINT (&Str1. ('Loan Balance' llbc mlbc hlbc) genc lficoc ('LTV' h85c h95c h100c cqc crc) ('Servicer' boac citic jpmc wellsc quickc) invc jumboc)

(&Str2. ('Loan Balance' llbd mlbd hlbd) gend lficod ('LTV' h85d h95d h100d cqd crd) ('Servicer' boad citid jpmd wellsd quickd) invd jumbod);

define cpn / group format=3.1;

define vint / order descending;

define llbc / sum format=FMTLLBC.;

define mlbc / sum format=FMTMLBC.;

define hlbc / sum format=FMTHLBC.;

define genc / sum format=FMTGENC.;

define lficoc / sum format=FMTLFICOC.;

define h85c / sum format=FMTH85C.;

define h95c / sum format=FMTH95C.;

define h100c / sum format=FMTH100C.;

define cqc / sum format=FMTCQC.;

define crc / sum format=FMTCRC.;

define boac / sum format=FMTBOAC.;

define citic / sum format=FMTCITIC.;

define jpmc / sum format=FMTJPMC.;

define wellsc / sum format=FMTWELLSC.;

define quickc / sum format=FMTQUICKC.;

define invc / sum format=FMTINVC.;

define jumboc / sum format=FMTJUMBOC.;

define llbd / sum format=FMTLLBD.;

define mlbd / sum format=FMTMLBD.;

define hlbd / sum format=FMTHLBD.;

define gend / sum format=FMTGEND.;

define lficod / sum format=FMTLFICOD.;

define h85d / sum format=FMTH85D.;

define h95d / sum format=FMTH95D.;

define h100d / sum format=FMTH100D.;

define cqd / sum format=FMTCQD.;

define crd / sum format=FMTCRD.;

define boad / sum format=FMTBOAD.;

define citid / sum format=FMTCITID.;

define jpmd / sum format=FMTJPMD.;

define wellsd / sum format=FMTWELLSD.;

define quickd / sum format=FMTQUICKD.;

define invd / sum format=FMTINVD.;

define jumbod / sum format=FMTJUMBOD.;

compute before / Style={font_size=0.3 background=lightgray just=left};

line 'FNMA 30-YEAR';

endcomp;

compute after cpn / Style={height=0.1in just=center};

line ' ';

endcomp;

run;

FOOTNOTE J=CENTER "Page ~{thispage} of ~{lastpage}";

ODS PDF CLOSE;

ODS listing;

Sorry I can not provide the data since it is confidential.

I want vertical line after vint, hlbc, genc, lficoc, crc, quickc, jumboc, hlbd, gend, lficod, crd, quickd.

For horizontal, I can live with my current method to leave space after cpn.

Thanks.

Jian

Re: How to control the location of horizontal and vertical lines of a table generated by proc report ...

Cynthia_sas — Mon, 17 Nov 2014 22:30:27 GMT

Hi:

If your data are confidential and if you cannot make some fake data to illustrate the issue and you cannot "recast" your report using one of the SASHELP datasets, then it might be better for you to just open a track with Tech Support. They can maintain the confidential nature of your data and help you figure out the best way to get the type of output you want for the PDF destination.

cynthia

Re: How to control the location of horizontal and vertical lines of a table generated by proc report ...

Cynthia_sas — Tue, 18 Nov 2014 18:34:26 GMT

Hi:

I had a chance to find the program that illustrates individual border control. So to do what you seem to want to do -- which is turn OFF all the interior lines except for certain cells/rows, you will have to use the "individual" border controls. This program just shows changing border colors for the table and then for individual NAME cells. But note that each destination will either use, or not use the specification for the NAME column.

This is probably going to mean that you have to do some work with Tech Support to get the correct borders turned on/off for different columns -- if it can be done for your destination of interest.

cynthia

ods html file="c:\temp\lines_special.html" style=sasweb;

ods pdf file="c:\temp\lines_special.pdf";

ods rtf file="c:\temp\lines_special.rtf";

title '4) Example with special lines';

** Example 4: various border colors and widths;

** Note how the different outputs look. Not all destinations;

** will use the different border colors for individual cells;

proc report data=sashelp.class nowd

style(report)={borderbottomcolor=cyan borderbottomwidth=5

bordertopcolor=yellow bordertopwidth=5

borderleftcolor=magenta borderleftwidth=5

borderrightcolor=black borderrightwidth=5};

where age le 12;

column name age height;

define name / style(column)={borderbottomcolor=red borderbottomwidth=5

bordertopcolor=green bordertopwidth=5

borderleftcolor=blue borderleftwidth=5

borderrightcolor=pink borderrightwidth=5};

run;

ods _all_ close;