ODS EXCEL/PROC REPORT : change background color of first row in worksheet

[elsfy]

Hi,

I did a proc report and i want to export it to an excel worksheet. No problem here.

Yet, i wanted to display the output starting at the second row (A2) of my excel worksheet. No problem here cause i added the START_AT suboption in ODS EXCEL.

ods excel file ="..." 
	options(sheet_interval='none', start_at='1,2') ;

So my proc report output starts at cell A2 in excel and the first row is empty. I want to change the background color of the entire first row into red to get something like this ; 

Thanks 

[Ksharp]

title color=red bcolor=red 'xxxx';
ods excel file ="c:\temp\temp.xlsx" 
	options(sheet_interval='none' embedded_titles='yes' start_at='1,1' hidden_rows="2") ;
proc report data=sashelp.class nowd ;run;
ods excel close;

[elsfy]

Hi,

Thank you for your code but it doesn't work exactly as i wanted.

It stops at column Q while my report is from column A to column Z. Do you know why ?

Thank you very much

[Ksharp]

NO. I get the right result !  what version sas are you using ?

You can contact support.sas.com  to confirm what is problem. 

Here is my code and snapshot .

data have;
 set sashelp.heart;
 array x{40} $ (40*'xxx');
run;

title color=red bcolor=red 'xxxx';
ods excel file ="c:\temp\temp.xlsx" 
	options(sheet_interval='none' embedded_titles='yes' start_at='1,1' hidden_rows="2") ;
proc report data=have(obs=10) nowd ;run;
ods excel close;

[elsfy]

I am using SAS v7.11.

Thank you very much for your help !

[Ksharp]

SAS version should like 9.4M4 , 9.4M5 ...

Using the following to check sas version.

%put &sysvlong4.;

But since I got no problem , I think I can't help you . Buy a ticket to  support.sas.com

Or @Cynthia_sas  could give you a hand .

[elsfy]

Sorry, i didn't check correctly. It's the 9.04v.
No problem, thank you again

[Cynthia_sas]

Hi:
It sounds like you might be using SAS Enterprise Guide. If @Ksharp gets the correct result, but when you run the SAME code, you get a different result, that is an indication that you might benefit more from working with Tech Support.
Cynthia

[elsfy]

Hi Cynthia,

Yes i am working with SAS Enterprise Guide. So i guess there is no other way to do this with SAS EG?
Thanks.

[Cynthia_sas]

Hi:
It looks like KSHARP made some fake data. The problem as I understand it is that you can get the first row in the color you want, but Ksharp's example, shows the spanning header going past column Q and your header for your data stops at column Q. Someone is going to have to look at ALL of your code and ALL of our ODS statements and use your data to figure out whether the issue is with your code, your data or your version of SAS. It seems to me that Ksharp was testing with his fake data and you were testing with your data because I don't see anyplace where you posted data or where he posted data. So I can't comment on whether it is possible to do what you want with SAS EG because Enterprise Guide is only a front end interface to SAS. You should be able to do what Ksharp demonstrated assuming that your data is similar and your code is similar and your version of SAS is similar.
Cynthia

[elsfy]

Is it possible to know how you would do that not only on the first row but the first 3 rows of the sheet ?
Or at least widen the first row ?

[Ksharp · Accepted solution]

Using two titles .

data have;
 set sashelp.heart;
 array x{40} $ (40*'xxx');
run;

title color=red bcolor=red 'xxxx';
title2 color=red bcolor=red 'xxxx';

ods excel file ="c:\temp\temp.xlsx" 
	options(sheet_interval='none' embedded_titles='yes' start_at='1,1' hidden_rows="3") ;
proc report data=have(obs=10) nowd ;run;
ods excel close;