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

Special offer for SAS Communities members

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.

 

View the full agenda.

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
  • 1334 views
  • 8 likes
  • 3 in conversation