How can I Change Background Color On Summary Line

SAS_Doctor — Tue, 12 Apr 2011 18:29:15 GMT

I am producing an Excel spreadsheet using DDE. How can I color the background of the summary line. Can I do it while I write the line? Plus how do I know which colors I can choose? I'm looking for a Currency Green.

One of the complexities is that the line numbers are dynamic because they will vary from run to run.
&num is the number of data lines.
&St_Cnt tells me how many break lines there are between the different states.
+7 is that the Header ends on line 7.

Here's my code:

FILENAME Rpt1 DDE "Excel|&PathIn.\[STM_ROI_Template.xlsx]STM_ROI!R8C1:R%eval(&num.+7+&St_Cnt.+1)C16" notab;
Data _NULL_;
file Rpt1;
set Formulas end=EOF;
by State DESCENDING YearMonth;
Put State '09'x
YearMonth '09'x
Recs_Sent '09'x
AE_Recs_Sent '09'x
AE_Recs_Rtnd '09'x
Recs_Rtnd '09'x
Recs_Updtd '09'x
AE_Obtnd '09'x
SSC_Dual_Obtnd '09'x
Tot_Dts_Obtnd '09'x
PctUpdtdRecs '09'x
CostPerRede '09'x
Tot_Cost '09'x
Val_Dts_Obtnd '09'x
PctGain '09'x
ROI '09'x
;
if last.State and not EOF
then do;
adjust_amt+1;
Put ' ' '09'x ' ' '09'x ' ' '09'x ' ' '09'x ' ' '09'x
' ' '09'x ' ' '09'x ' ' '09'x ' ' '09'x ' ' '09'x
' ' '09'x ' ' '09'x ' ' '09'x ' ' '09'x ' ' '09'x ' ';
end;
if EOF
then do;
Put 'AVERAGE' '09'x ' ' '09'x
"=AVERAGE(C8:C%eval(&num.+7+&St_Cnt.-1))" '09'x
"=AVERAGE(D8:D%eval(&num.+7+&St_Cnt.-1))" '09'x
"=AVERAGE(E8:E%eval(&num.+7+&St_Cnt.-1))" '09'x
"=AVERAGE(F8:F%eval(&num.+7+&St_Cnt.-1))" '09'x
"=AVERAGE(G8:G%eval(&num.+7+&St_Cnt.-1))" '09'x
"=AVERAGE(H8:H%eval(&num.+7+&St_Cnt.-1))" '09'x
"=AVERAGE(I8:I%eval(&num.+7+&St_Cnt.-1))" '09'x
"=$H%eval(&num.+7+&St_Cnt.)+$I%eval(&num.+7+&St_Cnt.)" '09'x
"=$J%eval(&num.+7+&St_Cnt.)/$G%eval(&num.+7+&St_Cnt.)" '09'x
"=$M%eval(&num.+7+&St_Cnt.)/$J%eval(&num.+7+&St_Cnt.)" '09'x
'=$B$2' '09'x
"=($H%eval(&num.+7+&St_Cnt.)+($I%eval(&num.+7+&St_Cnt.)*$B$3))*$B$1" '09'x
"=$P%eval(&num.+7+&St_Cnt.)/$M%eval(&num.+7+&St_Cnt.)" '09'x
"=$N%eval(&num.+7+&St_Cnt.)-$M%eval(&num.+7+&St_Cnt.)"
;
end;
run;

Re: How can I Change Background Color On Summary Line

Cynthia_sas — Thu, 14 Apr 2011 23:28:36 GMT

Hi:
I don't know how it works with DDE, but with PROC REPORT, it is simple to tell SAS to make the summary line a different color. You don't have to count up rows because SAS knows how many rows there are.

In the code below, I had to make some fake data so that every STATE would have a different number of observations. For the first PROC REPORT, I made a separate sheet for every STATE. For the second PROC REPORT, I put all 3 STATES in one worksheet with a blank line between each group.

cynthia
[pre]
data prdsale;
set sashelp.prdsale;
where region = 'EAST' and prodtype = 'OFFICE' and
product in ('DESK');
yearmonth= put(month,yymmdd5.);
if country = 'CANADA' then state = 'California';
else if country = 'GERMANY' then state='Georgia';
else state = 'New York';

** make some other variables for the report -- just fake data;
state2 = state;
yearmonth2 = yearmonth;
recs_sent = int(actual / 33);
recs_rtnd = int(predict / 15);
ae_obtnd = round(ranuni(0)*30,1);
ssc_dual_obtnd = round(ranuni(0)*4,1);
tot_cost=actual;
costperrede=predict;
** get different rows for differen values of state;
if state = 'Georgia' and quarter in (1,3,4) then delete;
if year = 1993 and state = 'California' then delete;
if year = 1993 and state = 'New York' then delete;
output;
run;

ods listing close;

proc sort data=prdsale out=prdsale;
by State DESCENDING YearMonth;
run;

ods tagsets.excelxp file='color_sum1.xls' style=sasweb
options(sheet_name='Wombat');

proc report data=prdsale nowd
style(header)={background=cx6b8e23 foreground=black};
title '1) Getting a separate Worksheet for Every BY Group';
by state;
column state2 yearmonth2 state yearmonth recs_sent recs_rtnd
ae_obtnd ssc_dual_obtnd tot_cost costperrede;
define state2 / display 'State';
define yearmonth2 / display 'Year Month' style(column)={cellwidth=1in};
define state / order noprint;
define yearmonth / order order=data noprint;
define recs_sent / mean 'Records Sent';
define recs_rtnd / mean;
define ae_obtnd / mean;
define ssc_dual_obtnd/mean;
define tot_cost / mean f=dollar15.2;
define costperrede/mean f=dollar15.2;
break after state/ summarize style={background=cx6b8e23};
compute after state;
state2 = 'Average';
endcomp;
run;

ods tagsets.excelxp options(sheet_name='All Groups');

proc report data=prdsale nowd
style(header)={background=cx6b8e23 foreground=black};
title '2) Getting All States in One Sheet with a separator line between each State';
column state2 yearmonth2 state yearmonth recs_sent recs_rtnd
ae_obtnd ssc_dual_obtnd tot_cost costperrede;
define state2 / display 'State';
define yearmonth2 / display 'Year Month' style(column)={cellwidth=1in};
define state / order noprint;
define yearmonth / order order=data noprint;
define recs_sent / mean 'Records Sent';
define recs_rtnd / mean;
define ae_obtnd / mean;
define ssc_dual_obtnd/mean;
define tot_cost / mean f=dollar15.2;
define costperrede/mean f=dollar15.2;
break after state/ summarize style={background=cx6b8e23};
compute after state ;
state2 = 'Average';
line ' ';
endcomp;
run;

ods _all_ close;
[/pre]

topic Re: How can I Change Background Color On Summary Line in Microsoft Integration with SAS

How can I Change Background Color On Summary Line

Re: How can I Change Background Color On Summary Line