topic Re: ODS EXCEL/PROC REPORT : change background color of first row in worksheet in SAS Programming

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

elsfy — Mon, 09 May 2022 09:54:09 GMT

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

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

Ksharp — Mon, 09 May 2022 11:11:05 GMT

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;

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

elsfy — Mon, 09 May 2022 14:21:02 GMT

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

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

Ksharp — Tue, 10 May 2022 03:39:35 GMT

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;

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

elsfy — Tue, 10 May 2022 09:06:21 GMT

I am using SAS v7.11.

Thank you very much for your help !

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

Ksharp — Tue, 10 May 2022 09:16:58 GMT

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 .

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

elsfy — Tue, 10 May 2022 09:32:44 GMT

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

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

Cynthia_sas — Tue, 10 May 2022 12:32:01 GMT

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

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

elsfy — Tue, 10 May 2022 12:34:39 GMT

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.

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

Cynthia_sas — Tue, 10 May 2022 12:41:35 GMT

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

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

elsfy — Tue, 10 May 2022 14:14:38 GMT

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 ?

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

Ksharp — Wed, 11 May 2022 02:40:35 GMT

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;