PROC Report , colors and groups

[jaxjordan]

I am trying to color code sections in a report.  

I can get the groups column A to color code correctly.  The first row in each group also print the proper color.  However, all other rows in the group are un-colored.  

The first column, section_number, is evaluated to determine which color value to use in my existing code.

Any help would be GREATLY appreciated.

Brad

Current Code:

%let blu=#DDEBF7;
%let yel=#FFF2CC;
%let grn=#E2EFDA;
%let pnk=#FFC7CE;

title;
footnote;
ods escapechar='^';
options center nodate nonumber orientation=landscape;
ods _all_ close;
ods listing close;
ods tagsets.excelxp file="\\t002pmid03\sasdata\jordan\temp\legend.xml"
style=sansprinter;

ods tagsets.excelxp options(frozen_headers='1'
autofilter='yes'
autofit_height='yes'
sheet_name="Legend"
absolute_column_width="30,10,40,40");

proc report data=legend spanrows split='~' noheader;
columns Section_Number Section_Name Column_ID Column_Name Column_Desc color;
define section_number / group order noprint;
define section_name / group;
define column_id / display;
define column_name / display;
define column_desc / display;
compute column_desc / char;
if section_number = 0 then do;
call define('section_name',"style","style={background=white vjust=m }");
call define('column_id' ,"style","style={background=white vjust=m }");
call define('column_name' ,"style","style={background=white vjust=m }");
call define('column_desc' ,"style","style={background=white vjust=m }");
end;
if section_number = 1 then do;
call define('section_name',"style","style={background=&blu vjust=m }");
call define('column_id' ,"style","style={background=&blu vjust=m }");
call define('column_name' ,"style","style={background=&blu vjust=m }");
call define('column_desc' ,"style","style={background=&blu vjust=m }");
end;
if section_number = 2 then do;
call define('section_name',"style","style={background=&yel vjust=m }");
call define('column_id' ,"style","style={background=&yel vjust=m }");
call define('column_name' ,"style","style={background=&yel vjust=m }");
call define('column_desc' ,"style","style={background=&yel vjust=m }");
end;
if section_number = 3 then do;
call define('section_name',"style","style={background=&grn vjust=m }");
call define('column_id' ,"style","style={background=&grn vjust=m }");
call define('column_name' ,"style","style={background=&grn vjust=m }");
call define('column_desc' ,"style","style={background=&grn vjust=m }");
end;
endcomp;
run;

ods _all_ close;

Current OutputDesired Output

[Cynthia_sas · Accepted solution]

Hi:
It looks to me as though your SECTION_NUMBER is a GROUP variable. That means it has a value at the beginning of the group (on the first row of a group), but PROC REPORT suppresses the value on the subsequent rows for the group -- which means that your test only works for the first of the group.

There is a way around that using "helper" variables with PROC REPORT. Basically, you'll need to "grab" and "hold" the value for your section number at the beginning of the group and put the value into a temporary helper variable and then do your comparison against that helper variables.

Something like this:
COMPUTE BEFORE section_number ;
   hold_snum = section_number ;
ENDCOMP ;

And, then, you'll have to use hold_snum in your IF statements for the CALL DEFINE.(Note, temporary variables are automatically retained in the PROC REPORT work area. So, you are essentially resetting and grabbing the section_number every time the group changes by using COMPUTE BEFORE like this.

Also, you have an issue in your DEFINE statement -- you have 2 usages GROUP and ORDER -- which one do you want? You should ONLY have 1 usage. My guess is that you want ORDER for Section_number AND Section_Name since all the other variables are a DISPLAY usage -- which usually implies ORDER for the category variables. You should see some notes in your log about the wrong usage for either or both section_number and section_name.

cynthia

[Christian_BCS]

I am attempting to use this code on a similar report.  I am running into a problem where hold_snum is not populating.  Here is my code.

proc report data=done spanrows NOWD list ls=100 box

style(header)=[fontstyle=italic fontsize=3]

style(column)=[fontfamily=helvetica fontsize=2]

style(report)=[fontsize=8pt];

column hold_snum catcount ZZCRCDESC ZZCRSDESC CTCLENDDT n pctn dummy;

define hold_snum / computed ;

define catcount / group ;

define ZZCRCDESC / group 'Category' ;

define ZZCRSDESC / group 'Disposition Code' ;

define CTCLENDDT / across " ";

define n / 'Total';

define pctn / 'Percent of Total' format=percent6.2;

COMPUTE before catcount;

hold_snum=catcount ;

endcomp;

break after ZZCRCDESC / dol dul skip summarize ;

rbreak after/summarize;

compute after ZZCRCDESC;

ZZCRCDESC = 'SubTotal';

endcomp;

compute after;

ZZCRCDESC = 'Grand Total';

endcomp;

　

compute ZZCRSDESC;

if missing(ZZCRSDESC) then do;

line_count=0;

call define(_row_,'style','style=[background=lightyellow]');

end;

endcomp;

run;

[Cynthia_sas]

Hi:
HOLD_SNUM should NOT be on the COLUMN statement. Temporary variables are variables that you use within a COMPUTE block, but they are NOT on a COLUMN statement. As soon as you put HOLD_SNUM on a COLUMN statement, it stops being a temporary variable whose value is retained.

AND, you have HOLD_SNUM on the COLUMN statement before CATCOUNT, the variable whose value you want to "grab" -- that violates the Left to Right rule of PROC REPORT. At the point in time when HOLD_SNUM in YOUR code example is being added to the report row, PROC REPORT had no visibility of  the value for CATCOUNT.

Also, you are mixing ODS style override options and LISTING options like DOL, DUL, SKIP and LS and BOX that will be IGNORED by ODS destinations.

Without data, nobody can run your code. However, in the code below, I've used SASHELP.SHOES, so anyone can run it.

Note that I do NOT have hold_reg or hold_prd temporary variables on the COLUMN statement. They are used exclusively in the COMPUTE block to hold and then use the values for region and product.

Here's the code:

proc sort data=sashelp.shoes out=newshoes;
  where region in ('Western Europe' 'Eastern Europe') and product in ('Sandal' 'Slipper');
  by region product;
run;
  
** run 1 time, observe how hold_reg and hold_prd temporary variables;
** are used. Then remove comments from noprint option and rerun again;
proc report data=newshoes;
  column region product showall showprod sales;
  define region / group  "Default Region" /* noprint */;
  define product / order "Default Product" /* noprint */;
  define showall / computed "Computed Region" ;
  define showprod / computed "Computed Product";
  define sales / sum;
  compute before region;
     length hold_reg $25;
     hold_reg = region;
  endcomp;
  compute before product;
     length hold_prd $25;
     hold_prd = product;
  endcomp;
  compute showall / character length=25;
    showall = hold_reg;
  endcomp;
  compute showprod / character length=25;
    showprod = hold_prd;
  endcomp;
  break after region / summarize style=Header{background=lightyellow};
  break after product / summarize style=Header{color=black};
  rbreak after / summarize style=Header{background=lightgreen};
  compute after region;
    showall = catx(' ','Total',hold_reg);
	showprod = 'All Products';
    ** how to simulate a SKIP in ODS output;
    line ' ';
  endcomp;
  compute after product;
    showprod = 'SubTotal';
  endcomp;
  compute after;
    showall = 'Grand Total';
	showprod = 'All Regions';
  endcomp;
run;

Here's the output:

 Hope this helps,

Cynthia