PROC REPORT DATA=MASTER_DST_REMOTE_XSELL_FINAL nowd spanrows
style(report)=[JUST=CENTER OUTPUTWIDTH=95% CELLSPACING=2 BORDERCOLOR=BLACK BORDERWIDTH=2]
STYLE(HEADER)={BORDERCOLOR=BLACK FOREGROUND=WHITE BACKGROUND=purple FONT=("zurich BT",11pt)}
STYLE(COLUMN)={TAGATTR="WRAP" JUST=CENTER FONT=("zurich BT",08pt) OUTPUTWIDTH=0.05IN
FOREGROUND=BLACK BORDERCOLOR=BLACK};
TITLE FONT="Zurich BT" bold height=6 "<U>DATE-WISE NTB/ETB ONBOARDING SUMMARY </U>" JUSTIFY=CENTER;
TITLE2 FONT="Zurich BT" bold height=2 "<U>Report Run Date: &sysdate. Time: &SYSTIME</U>" JUSTIFY=RIGHT;
col (DATE) ('DST' DST_NTB 'DST_ETB(AC)'N 'DST_ETB(LC)'N)
('X-SELL' 'X-SELL_NTB'N 'X-SELL_ETB(AC)'N 'X-SELL_ETB(LC)'N)
('REMOTE' 'REMOTE_ETB(LC)'N)
('GRAND TOTAL' TOTAL_NTB 'TOTAL_ETB(AC)'N 'TOTAL_ETB(LC)'N);
define DATE/"DATE" order order=data style(column)=[font=("zurich BT",9pt) backgroundcolor= whitesmoke];
define DST_NTB/group ANALYSIS display"NTB" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightblue];
define 'DST_ETB(AC)'N/group ANALYSIS display"ETB(AC)" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightblue];
define 'DST_ETB(LC)'N/group ANALYSIS display"ETB(LC)" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightblue];
define 'X-SELL_NTB'N/group ANALYSIS display"NTB" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightgoldenrodyellow];
define 'X-SELL_ETB(AC)'N/group ANALYSIS display "ETB(AC)" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightgoldenrodyellow];
define 'X-SELL_ETB(LC)'N/group ANALYSIS display"ETB(LC)" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightgoldenrodyellow];
define 'REMOTE_ETB(LC)'N/group ANALYSIS display"ETB(LC)" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightcoral];
define TOTAL_NTB/group ANALYSIS "NTB" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightgreen];
define 'TOTAL_ETB(AC)'N/group ANALYSIS "ETB(AC)" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightgreen];
define 'TOTAL_ETB(LC)'N/group ANALYSIS"ETB(LC)" style(column)=[font=("zurich BT",9pt) backgroundcolor= lightgreen];
/* compute */
compute DST_NTB;
if DST_NTB<10 then call define(_col_, "style", "style={background=red}");
endcomp;
compute 'DST_ETB(AC)'N;
if 'DST_ETB(AC)'N<10 then call define(_col_, "style", "style={background=red}");
endcomp;
compute 'DST_ETB(LC)'N;
if 'DST_ETB(AC)'N<10 then call define(_col_, "style", "style={background=red}");
endcomp;
compute 'X-SELL_NTB'N;
if 'X-SELL_NTB'N<10 then call define(_col_, "style", "style={background=red}");
endcomp;
compute 'X-SELL_ETB(AC)'N;
if 'X-SELL_ETB(AC)'N<10 then call define(_col_, "style", "style={background=red}");
endcomp;
compute 'X-SELL_ETB(LC)'N;
if 'X-SELL_ETB(LC)'N<10 then call define(_col_, "style", "style={background=red}");
endcomp;
compute 'REMOTE_ETB(LC)'N;
if 'REMOTE_ETB(LC)'N<10 then call define(_col_, "style", "style={background=red}");
endcomp;
rbreak after /summarize ol skip;
compute after ;
if _BREAK_ eq '_RBREAK_' then do;
DATE = 'Total';
CALL DEFINE(_row_ ,"style","style=[BACKGROUND=lightorange FOREGROUND=WHITE]");
end;
endcomp;
RUN;
It's doing exactly what I want but at the end I need a grand total and its doing this. In the columns where I didnt apply compute its showing the total but In the columns where condition <10 is applied its turning that in red too.
Any solutions. Please! help 😐
Hi:
There are some issues in your code that are problematic. First, you are mixing LISTING only options, like OL, SKIP with HTML tag syntax (the U tags in the TITLE), with STYLE overrides and also using TAGATTR. You don't show all your code, so depending on which destination you're using in your ODS statements, some of the syntax will be ignored (for example, the LISTING options like OL and SKIP will be ignored by any ODS destinations. Your use of TAGATTR implies that you might be using ODS EXCEL, but then your use of the HTML tags in the TITLE and the other style overrides implies that you might be just using ODS HTML.
Also, your PROC REPORT syntax is not specifying the correct usage for most of your DEFINE statements. For example, you have this:
define DST_NTB/group ANALYSIS display "NTB" ... more code ... ;
And this is incorrect. The GROUP usage is specified when you want to create GROUPS for the purpose of collapsing rows. For example, compare these 2 outputs:
#5 is what happens when you don't account for the _RBREAK_ in your COMPUTE bloc. Then, #6 accomplishes what you want to do. Also, I used a computed variable called SHOWAGE to turn the numeric AGE value into a character string, so that the Grand Total string could be assigned in a COMPUTE AFTER.
My other code examples show you the difference between DISPLAY, ORDER and GROUP usage and how that impacts summarizing too.
You did not post data, but I just used a subset of SASHELP.CLASS.
ods html path='c:\temp' file='show_usage.html';
proc report data=sashelp.class spanrows;
title '1) Will see 1 row for every obs in subset with usage of ORDER';
where age in (12, 14, 15);
column age n height weight;
define age / order;
define n / 'Count';
define height / analysis sum f=5.1;
define weight / analysis sum f=5.1;
rbreak after / summarize;
run;
proc report data=sashelp.class spanrows;
title '1a) Usage is completely wrong for HEIGHT and WEIGHT -- no summarizing at RBREAK for HEIGHT or WEIGHT';
where age in (12, 14, 15);
column age n height weight;
define age / order;
define n / 'Count';
define height / group analysis display f=5.1;
define weight /group analysis display f=5.1;
rbreak after / summarize;
run;
proc report data=sashelp.class spanrows;
title '2) Will see 1 row for every value of AGE with usage of GROUP';
where age in (12, 14, 15);
column age n height weight;
define age / group;
define n / 'Count';
define height / analysis sum f=5.1;
define weight / analysis sum f=5.1;
rbreak after / summarize;
run;
proc report data=sashelp.class spanrows;
title '3) Now I can change the summarized statistics';
title2 'But COMPUTE AFTER does NOT work because AGE is numeric';
where age in (12, 14, 15);
column age n height weight;
define age / group;
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute after;
age = 'Grand Total';
endcomp;
run;
proc report data=sashelp.class spanrows;
title '4) One way to get GRAND TOTAL at summary line';
where age in (12, 14, 15);
column age showage n height weight;
define age / group noprint;
define showage / computed 'Age'
style(column)={just=r};
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute showage/character length=11;
if age gt ' ' then showage = put(age,2.0);
endcomp;
compute after;
showage = 'Grand Total';
endcomp;
run;
proc report data=sashelp.class spanrows
style(summary)={background=darkorange color=white};
title '5) change colors but RBREAK line is also changed';
where age in (12, 14, 15);
column age showage n height weight;
define age / group noprint;
define showage / computed 'Age'
style(column)={just=r};
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute showage/character length=11;
if age gt ' ' then showage = put(age,2.0);
endcomp;
compute n;
if n ge 5 then call define(_col_,'style','style={background=red}');
endcomp;
compute height;
if height.mean gt 60 then call define(_col_,'style','style={background=red}');
endcomp;
compute weight;
if weight.min le 80 then call define(_col_,'style','style={background=red}');
endcomp;
compute after;
showage = 'Grand Total';
endcomp;
run;
proc report data=sashelp.class spanrows
style(summary)={background=darkorange color=white};
title '6) change colors correctly without impacting RBREAK line';
where age in (12, 14, 15);
column age showage n height weight;
define age / group noprint;
define showage / computed 'Age'
style(column)={just=r};
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute showage/character length=11;
if age gt ' ' then showage = put(age,2.0);
endcomp;
compute n;
if _break_ ne '_RBREAK_' and n ge 5 then do;
call define(_col_,'style','style={background=red}');
end;
endcomp;
compute height;
if _break_ ne '_RBREAK_' and height.mean gt 60 then do;
call define(_col_,'style','style={background=red}');
end;
endcomp;
compute weight;
if _break_ ne '_RBREAK_' and weight.min le 80 then do;
call define(_col_,'style','style={background=red}');
end;
endcomp;
compute after;
showage = 'Grand Total';
endcomp;
run;
ods html close;
I think after you correct your syntax that's wrong and clean up the usage and only use options that are appropriate for your destination, you'll get the report you want.
Cynthia
Hi:
There are some issues in your code that are problematic. First, you are mixing LISTING only options, like OL, SKIP with HTML tag syntax (the U tags in the TITLE), with STYLE overrides and also using TAGATTR. You don't show all your code, so depending on which destination you're using in your ODS statements, some of the syntax will be ignored (for example, the LISTING options like OL and SKIP will be ignored by any ODS destinations. Your use of TAGATTR implies that you might be using ODS EXCEL, but then your use of the HTML tags in the TITLE and the other style overrides implies that you might be just using ODS HTML.
Also, your PROC REPORT syntax is not specifying the correct usage for most of your DEFINE statements. For example, you have this:
define DST_NTB/group ANALYSIS display "NTB" ... more code ... ;
And this is incorrect. The GROUP usage is specified when you want to create GROUPS for the purpose of collapsing rows. For example, compare these 2 outputs:
#5 is what happens when you don't account for the _RBREAK_ in your COMPUTE bloc. Then, #6 accomplishes what you want to do. Also, I used a computed variable called SHOWAGE to turn the numeric AGE value into a character string, so that the Grand Total string could be assigned in a COMPUTE AFTER.
My other code examples show you the difference between DISPLAY, ORDER and GROUP usage and how that impacts summarizing too.
You did not post data, but I just used a subset of SASHELP.CLASS.
ods html path='c:\temp' file='show_usage.html';
proc report data=sashelp.class spanrows;
title '1) Will see 1 row for every obs in subset with usage of ORDER';
where age in (12, 14, 15);
column age n height weight;
define age / order;
define n / 'Count';
define height / analysis sum f=5.1;
define weight / analysis sum f=5.1;
rbreak after / summarize;
run;
proc report data=sashelp.class spanrows;
title '1a) Usage is completely wrong for HEIGHT and WEIGHT -- no summarizing at RBREAK for HEIGHT or WEIGHT';
where age in (12, 14, 15);
column age n height weight;
define age / order;
define n / 'Count';
define height / group analysis display f=5.1;
define weight /group analysis display f=5.1;
rbreak after / summarize;
run;
proc report data=sashelp.class spanrows;
title '2) Will see 1 row for every value of AGE with usage of GROUP';
where age in (12, 14, 15);
column age n height weight;
define age / group;
define n / 'Count';
define height / analysis sum f=5.1;
define weight / analysis sum f=5.1;
rbreak after / summarize;
run;
proc report data=sashelp.class spanrows;
title '3) Now I can change the summarized statistics';
title2 'But COMPUTE AFTER does NOT work because AGE is numeric';
where age in (12, 14, 15);
column age n height weight;
define age / group;
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute after;
age = 'Grand Total';
endcomp;
run;
proc report data=sashelp.class spanrows;
title '4) One way to get GRAND TOTAL at summary line';
where age in (12, 14, 15);
column age showage n height weight;
define age / group noprint;
define showage / computed 'Age'
style(column)={just=r};
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute showage/character length=11;
if age gt ' ' then showage = put(age,2.0);
endcomp;
compute after;
showage = 'Grand Total';
endcomp;
run;
proc report data=sashelp.class spanrows
style(summary)={background=darkorange color=white};
title '5) change colors but RBREAK line is also changed';
where age in (12, 14, 15);
column age showage n height weight;
define age / group noprint;
define showage / computed 'Age'
style(column)={just=r};
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute showage/character length=11;
if age gt ' ' then showage = put(age,2.0);
endcomp;
compute n;
if n ge 5 then call define(_col_,'style','style={background=red}');
endcomp;
compute height;
if height.mean gt 60 then call define(_col_,'style','style={background=red}');
endcomp;
compute weight;
if weight.min le 80 then call define(_col_,'style','style={background=red}');
endcomp;
compute after;
showage = 'Grand Total';
endcomp;
run;
proc report data=sashelp.class spanrows
style(summary)={background=darkorange color=white};
title '6) change colors correctly without impacting RBREAK line';
where age in (12, 14, 15);
column age showage n height weight;
define age / group noprint;
define showage / computed 'Age'
style(column)={just=r};
define n / 'Count';
define height / analysis mean 'Avg Ht' f=5.1;
define weight / analysis min 'Min Wt' f=5.1;
rbreak after / summarize;
compute showage/character length=11;
if age gt ' ' then showage = put(age,2.0);
endcomp;
compute n;
if _break_ ne '_RBREAK_' and n ge 5 then do;
call define(_col_,'style','style={background=red}');
end;
endcomp;
compute height;
if _break_ ne '_RBREAK_' and height.mean gt 60 then do;
call define(_col_,'style','style={background=red}');
end;
endcomp;
compute weight;
if _break_ ne '_RBREAK_' and weight.min le 80 then do;
call define(_col_,'style','style={background=red}');
end;
endcomp;
compute after;
showage = 'Grand Total';
endcomp;
run;
ods html close;
I think after you correct your syntax that's wrong and clean up the usage and only use options that are appropriate for your destination, you'll get the report you want.
Cynthia
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.