ODS and Base Reporting

Build reports by using ODS to create HTML, PDF, RTF, Excel, text reports and more!
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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;

 

PaigeMiller_0-1717415919519.png

 

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

Ksharp_0-1717468618042.png

 

View solution in original post

3 REPLIES 3
Ksharp
Super User

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;

Ksharp_0-1717468618042.png

 

PaigeMiller
Diamond | Level 26

Very nice, @Ksharp . Thanks!

--
Paige Miller
Cynthia_sas
SAS Super FREQ
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

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1425 views
  • 8 likes
  • 3 in conversation