topic Re: SAS ODS Excel Proc Tabulate Border Thickness by group in ODS and Base Reporting

SAS ODS Excel Proc Tabulate Border Thickness by group

Reg1na — Tue, 11 Jul 2017 22:50:31 GMT

Hello,

I am creating a report using proc tabulate in SAS Studio 9.4. Below is the sample code that I run:

DATA EXAMPLE;
INPUT LOCK $3. MONTH_TYPE $ LEVEL $ CHANNEL $ PPL;
DATALINES;
May Type1 lvl1 chn1 200
May Type1 lvl1 chn1 30
May Type2 lvl1 chn1 4
May Type2 lvl1 chn1 45
Jun Type1 lvl1 chn1 67
Jun Type1 lvl1 chn1 1
Jun Type2 lvl1 chn1 6
Jun Type2 lvl1 chn1 8
May Type1 lvl1 chn2 76
May Type1 lvl1 chn2 45
May Type2 lvl1 chn2 90
May Type2 lvl1 chn2 76
Jun Type1 lvl1 chn2 56
Jun Type1 lvl1 chn2 67
Jun Type2 lvl1 chn2 90
Jun Type2 lvl1 chn2 43
May Type1 lvl2 chn1 66
May Type1 lvl2 chn1 55
May Type2 lvl2 chn1 33
May Type2 lvl2 chn1 22
Jun Type1 lvl2 chn1 34
Jun Type1 lvl2 chn1 23
Jun Type2 lvl2 chn1 76
Jun Type2 lvl2 chn1 87
May Type1 lvl2 chn2 100
May Type1 lvl2 chn2 56
May Type2 lvl2 chn2 13
May Type2 lvl2 chn2 27
Jun Type1 lvl2 chn2 26
Jun Type1 lvl2 chn2 45
Jun Type2 lvl2 chn2 87
Jun Type2 lvl2 chn2 91
;
RUN;

PROC FORMAT;
	VALUE $CHANNELfmt
		'chn1'='Channel1'
		'chn2'='Channel2'
;
	VALUE $CHANNELcolor 
		'chn1'='White'
		'chn2' = 'GWH'
;
RUN;


/* ODS EXCEL FILE="....example.xlsx" */
/*  */
/*    OPTIONS */
/* 	( */
/* 	ABSOLUTE_COLUMN_WIDTH='15' */
/* 	ABSOLUTE_ROW_HEIGHT='20' */
/*    	SHEET_INTERVAL="NONE" */
/*     SHEET_LABEL="Test" */
/*     ); */

	PROC TABULATE DATA=EXAMPLE S=[JUST=C VJUST=C TAGATTR="WRAP:NO"];
	CLASS MONTH_TYPE LOCK LEVEL CHANNEL;
	CLASSLEV MONTH_TYPE/STYLE=[FONT_STYLE=ITALIC JUST=R VJUST=C FONT_FACE='Arial' TAGATTR="WRAP:NO"];
	CLASSLEV LEVEL/STYLE=[BACKGROUND=LILG JUST=C VJUST=C FONTSIZE=3.7 FONT_FACE='Arial' TAGATTR="WRAP:NO"];
	CLASSLEV LOCK/STYLE=[JUST=C VJUST=C FONTSIZE=3 FONT_FACE='Arial' TAGATTR="WRAP:NO" BACKGROUND=YWH];
	CLASSLEV CHANNEL/STYLE=[JUST=C VJUST=C FONTSIZE=3.5 FONT_FACE='Arial' BACKGROUND=$CHANNELcolor. TAGATTR="WRAP:NO"];
	FORMAT CHANNEL $CHANNELfmt. LEVEL $LEVEL.;
	VAR PPL/STYLE=<PARENT>;
	TABLE 
	MONTH_TYPE=''*LOCK='',
	LEVEL=''*CHANNEL=''*PPL='' *F=COMMA10.*[STYLE=<PARENT>[FONTSIZE=2 FONT_FACE='Arial' TAGATTR="WRAP:NO"]]
	/RTS=25 ROW=FLOAT 
	BOX=[LABEL="Test" STYLE=[FONTSIZE=3.7 FONT_FACE='Arial' BACKGROUND=BIGB FOREGROUND=WHITE TAGATTR="WRAP:NO"]];
	KEYLABEL SUM=' ';
	KEYWORD SUM/STYLE=<PARENT>;
	RUN;
	
/* ODS EXCEL CLOSE;	 */

I commented out excel output portion of it but it works well and I get Excel file. I want to have thick borders around class variable level. In other words I get:

And I would like to have this:

It does not have to be red, I want to have a more readable report that separates lvl1 and lvl2 with thicker borders. Can anyone help?

Thank you,

Regina.

Re: SAS ODS Excel Proc Tabulate Border Thickness by group

ABritinAus — Thu, 20 Jul 2017 04:12:39 GMT

Hello

Hope this isn't too late.

Try adding the following formats:

	VALUE $CHANNELleftborder
		'chn1'='red'
		'chn2' = ' '
;

	VALUE $CHANNELrightborder 
		'chn1'=' '
		'chn2' = 'red'
;

	VALUE $CHANNELleftwidth 
		'chn1'='1'
		'chn2' = '0.1'
;

	VALUE $CHANNELrightwidth
		'chn1'='0.1'
		'chn2' = '1'
;

and then the following code in the proc report:

CLASSLEV LEVEL/STYLE=[BACKGROUND=LILG JUST=C VJUST=C FONTSIZE=3.7 FONT_FACE='Arial' TAGATTR="WRAP:NO" bordertopcolor=red borderleftcolor=red borderrightcolor=red bordertopwidth=1 borderrightwidth=1 borderleftwidth=1 borderbottomwidth=0.1];

CLASSLEV CHANNEL/STYLE=[JUST=C VJUST=C FONTSIZE=3.5 FONT_FACE='Arial' BACKGROUND=$CHANNELcolor. 
    borderleftcolor=$CHANNELleftborder. borderrightcolor=$CHANNELrightborder. borderleftwidth=$CHANNELleftwidth. borderrightwidth=$CHANNELrightwidth. TAGATTR="WRAP:NO"];

This should give what you want in excel. It may not look right on the screen though.

Note that in ODS EXCEL borderwidths or 0-0.29 gives thin borders, 0.3 to 0.49 gives medium borders and 0.5-high gives thick borders.

Hope it helps.

Note that getting the bottom border for that whole area, or for a table in general is tricky but hopefully the borders this provides will help.

Re: SAS ODS Excel Proc Tabulate Border Thickness by group

Reg1na — Thu, 20 Jul 2017 18:04:35 GMT

Thank you very much, I really appreciate it. It works well and gives desired result!

Re: SAS ODS Excel Proc Tabulate Border Thickness by group

Reg1na — Thu, 20 Jul 2017 18:33:56 GMT

I wonder if there is an option that makes outside table borders thick. That way my borderbottomwidth issue would be resolved.. I am looking at frame= and rules= but it doesn't seem to be working