- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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()=.
Paige Miller
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Very nice, @Ksharp . Thanks!
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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