The SAS Output Delivery System and reporting techniques

PROC Report , colors and groups

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

PROC Report , colors and groups

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;

 

 

CurrentOutput.jpgCurrent OutputDesiredOutput.jpgDesired Output

 

 

 

 


Accepted Solutions
Solution
a month ago
SAS Super FREQ
Posts: 9,424

Re: PROC Report , colors and groups

[ Edited ]
Posted in reply to jaxjordan

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

View solution in original post


All Replies
Solution
a month ago
SAS Super FREQ
Posts: 9,424

Re: PROC Report , colors and groups

[ Edited ]
Posted in reply to jaxjordan

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 101 views
  • 0 likes
  • 2 in conversation