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,
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;
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;
Very nice, @Ksharp . Thanks!
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.