topic Re: Create a blank column shaded grey in PROC REPORT/ODS EXCEL in ODS and Base Reporting

Create a blank column shaded grey in PROC REPORT/ODS EXCEL

PaigeMiller — Mon, 03 Jun 2024 12:56:04 GMT

Suppose, because I would like to create some visual separation between groups of columns in PROC REPORT output, I want a column that is shaded gray and has no data in it. I can get most of the way there via this program. Note the use of the fake variable GAP in the columns statement, which produces the empty column F in the Excel output. But you can see the gray appears for the DATA rows but not for the header rows. In PROC REPORT, I use style(header) but it doesn't produce the desired results. So how can I get PROC REPORT to shade the first two rows of column F the same color gray?

proc format;
    value gapf .,low-high=' ';
run;

ods excel file="test.xlsx";
proc report data=sashelp.cars(obs=50);
    columns make msrp,type gap invoice,type;
    define make/group "Make";
    define type/across " ";
    define gap/' ' format=gapf. style(column)={cellwidth=.01in backgroundcolor=verylightgray}
         style(header)={backgroundcolor=verylightgray};
    define msrp/mean "MSRP";
    define invoice/mean "Invoice";
run;
ods excel close;

Also, how can I make column F narrower, the option cellwidth=.01in doesn't seem to make it that narrow? Is this too narrow in Excel? EDIT: it appears I can narrow the column width via the ABSOLUTE_COLUMN_WIDTH option of ODS EXCEL, but that's tedious, it still would be better if I could do it in PROC REPORT with STYLE()=.

Re: Create a blank column shaded grey in PROC REPORT/ODS EXCEL

Ksharp — Tue, 04 Jun 2024 02:42:16 GMT

Paige,

You could use Traffic Light to get it .

But for your second question, I think you have to use "ABSOLUTE_COLUMN_WIDTH ", or maybe @Cynthia_sas knew something I don't know.


proc format;
value $fmt
'd'='verylightgray'
;
run;

ods excel file="c:\temp\test.xlsx";
proc report data=sashelp.cars(obs=50) nowd style(header)={foreground=$fmt. backgroundcolor=$fmt.};
    columns make msrp,type ('d' (' ' dummy)) invoice,type;
    define make/group "Make";
    define type/across " ";
    define dummy/computed 'd' style(column header)={ backgroundcolor=verylightgray};
    define msrp/mean "MSRP";
    define invoice/mean "Invoice";
	compute dummy/character length=1;
	 dummy=' ';
	endcomp;
run;
ods excel close;

Re: Create a blank column shaded grey in PROC REPORT/ODS EXCEL

PaigeMiller — Tue, 04 Jun 2024 09:55:42 GMT

Very nice, @Ksharp . Thanks!

Re: Create a blank column shaded grey in PROC REPORT/ODS EXCEL

Cynthia_sas — Tue, 04 Jun 2024 14:00:04 GMT

Hi:
Ksharp's solution is the technique that I suggest if you need to control spanning headers with a different color (spanning headers is where the empty blue header for the GAP column comes from on the row with MSRP and Invoice). Absolute_Column_Width is the only way I know to force Excel to change width when cellwidth doesn't work.
Cynthia