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]
