topic Re: Adding colors to the rows in Excel in SAS Procedures

Adding colors to the rows in Excel

Sandhya — Thu, 13 May 2010 15:25:53 GMT

Hi,

I have a dataset which is ODSed to EXCEL.

The dataset looks like this.

Group Panel Value Mean
111 c 11.11 22.22
111 c 12.12 21.21
111 b 13.13 13.45
222 a 45.45 56.56
222 d 77.77 77.77
222 d 88.55 66.66
..........

Now I need to color the dataset in which respect to Panel.

Meaning all panel c's under 111 gets a color, all panel b's under 111 get a different color.

Please give me suggestions.

Thanks in advance,
Sandhya.

Re: Adding colors to the rows in Excel

Cynthia_sas — Thu, 13 May 2010 16:10:11 GMT

Hi:
I'm confused by your description of what you want. Your data/report looks like this:
[pre]
Group Panel Value Mean
111 c 11.11 22.22 <---pink
111 c 12.12 21.21 <---pink
111 b 13.13 13.45 <---yellow
222 a 45.45 56.56 <--- what color for these???
222 d 77.77 77.77
222 d 88.55 66.66
[/pre]

Are you saying that for group 111, when panel = c you want one color??? And then for Group=111 and panel=b, you want a different color??

And then WHAT do you want to color??? the panel cell??? You want to color the group cell and the panel cell??? Or you want to color the whole row???

Also, you indicate that you are using ODS to create a file that Excel can open...but are you using:
ODS CSV
ODS HTML
ODS MSOFFICE2K
ODS TAGSETS.EXCELXP?????

And, also, what is your procedure of choice?? Are you using PROC PRINT, PROC REPORT, PROC TABULATE?????

cynthia

Re: Adding colors to the rows in Excel

Warren — Sat, 15 May 2010 20:01:49 GMT

I have a similar question. I need to create a excel spreadsheet with a light green color for the whole row whenever desc = 'diff' as well as the title row. What should I do? Please see code below.

data diff(keep=n desc m2 m1 m0);
merge a b;
by n;
dm2 = sum(m2 , -bm2);
dm1 = sum(m1 , -bm1);
dm0 = sum(m0 , -bm0);
if abs(dm2 ) > 2
or abs(dm1 ) > 2
or abs(dm0 ) > 2;
output;
desc = bdesc;
m2 = bm2;
m1 = bm1;
m0 = bm0;
output;
desc = 'diff';
m2 = dm2;
m1 = dm1;
m0 = dm0;
output;
run;

proc export data=diff
outfile="c:\diff.xls"
dbms=excel2000 replace;
run;

Thanks.

Warren

Re: Adding colors to the rows in Excel

Cynthia_sas — Sat, 15 May 2010 21:02:31 GMT

Hi,
The issue is that there are no colors and fonts in a SAS dataset. So with PROC EXPORT there are no ways to highlight or do traffic lighting or conditional highlighting.

If you use ODS and PROC REPORT then you can highlight a whole row based on a condition, using the CALL DEFINE statement in a COMPUTE block.

There are examples in previous forum postings if you search for traffic lighting or conditional highlighting, as well as in the PROC REPORT documentation.

Cynthia

Re: Adding colors to the rows in Excel

Warren — Mon, 17 May 2010 17:08:28 GMT

I've found that I can use ods tagsets.excelxp with proc report and the following compute statement.

compute src;
if src = 'diff' then call define(_row_, "style", "style={foreground=blue}");
endcomp;

See also http://support.sas.com/kb/23/353.html