topic Re: Bolding One Cell Value Based on Comparison with Another in PROC REPORT in ODS and Base Reporting

Bolding One Cell Value Based on Comparison with Another in PROC REPORT

tburus — Wed, 19 Aug 2020 05:27:13 GMT

I have a PROC REPORT table in which I want to bold cell based on comparison with other cells. Here is the code and current output.

Code:

proc report data=gender_t2c nowd split="*"
		style(report)=[frame=hsides rules=none]
		style(header)=[background=white color=&rgreen. borderbottomcolor=black]
		style(lines)=[background=white];
	column injsite boys_soccer girls_soccer estimate lowercl uppercl ci;
	define injsite / 'Body Site' group order = data;
	define boys_soccer / "Boys' Soccer" analysis pctsum format=pct. style(column)=[cellwidth=1.5in just=c];
	define girls_soccer / "Girls' Soccer" analysis pctsum format=pct. style(column)=[cellwidth=1.5in just=c];
	define estimate / '' display noprint;
	define lowercl / '' display noprint;
	define uppercl / '' display noprint;
	define ci / "IPR (95% CI)" display style(column)=[cellwidth=1.5in just=c];

	rbreak after / summarize style(summary)={font_weight=bold};
	compute after;
		injsite = 'Total';
	endcomp;
run;

Output:

There are two comparisons/boldings that I want to perform:

Bold the cell value between Boys' Soccer and Girls' Soccer that is the largest
Bold any IPRs where 1 is not in the interval given

I am exploring how to do this with COMPUTE and CALL DEFINE commands, but cannot quite make it work.

Re: Bolding One Cell Value Based on Comparison with Another in PROC REPORT

ballardw — Wed, 19 Aug 2020 11:51:29 GMT

Provide example data in the form of a data step.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

Re: Bolding One Cell Value Based on Comparison with Another in PROC REPORT

Cynthia_sas — Wed, 19 Aug 2020 14:35:33 GMT

Hi:
What code have you tried? You said you can't make it work. Nobody else can make it work without making up some fake data including your NOPRINT variables which do not seem to be used. PROC REPORT has 2 rules that you need to consider before you try to apply changes with CALL DEFINE:
1) you have to refer to analysis items by their "internal name" -- which is variable.statistic
2) PROC REPORT has a "left to right" rule -- each item is placed on the report row, one item at a time. So for example, with this column statement:
column cat a b tot;
at the point in time that PROC REPORT is writing CAT on the report row, it has no visibility of the values of A, B or TOT. When Proc Report places A on the report row, then it has visibility of only the value for A and CAT (there is no visibility for B or TOT yet). So working from left to right you can't do a comparison between A and B (for example) until both A and B have been placed on the report row. This means a comparison will fail if placed in a COMPUTE block for A, but will work when placed in the COMPUTE block for B or TOT (which are to the right of A on the report row).

Consider this example with fake data, since you did not show all your code or provide any data:

Note the use of A.SUM and B.SUM in the code because both A and B are analysis items and the statistic requested is SUM. If I had requested the MEAN, then the reference would have been A.MEAN and B.MEAN, etc.
Hope this helps,
Cynthia

Re: Bolding One Cell Value Based on Comparison with Another in PROC REPORT

tburus — Wed, 19 Aug 2020 15:20:16 GMT

I have crafted a test set to explain what I mean (the NOPRINT columns are something else I'm working with that's already settled).

data test;
input cat $ a b;
datalines;
A 4 5 
B 2 7
C 8 4
D 1 9
;
run;

proc report data=test nowd;
	column cat a b;
	define cat / '' group order = data;
	define a / 'a' analysis pctsum format=percent8.1;
	define b / 'b' analysis pctsum format=percent8.1;

	compute b;
		if b > a then call define(_col_,'style','style={font_weight=bold}');
	endcomp;

	rbreak after / summarize;
run;

The COMPUTE block is where I need help. I want it to compare the PCTSUM in each row for a and b. If the PCTSUM of b is larger, I want to bold b in that row; if the PCTSUM of a is larger, I want to bold a in that row. (It's highly unlikely that they will be equal in my application, but I guess we could make the first condition greater than or equal to.) Thanks.

Re: Bolding One Cell Value Based on Comparison with Another in PROC REPORT

ballardw — Wed, 19 Aug 2020 17:43:30 GMT

Since font weight isn't always obvious enough I colored the cell yellow or pink:

proc report data=test nowd;
	column cat a b;
	define cat / '' group order = data;
	define a / 'a' analysis pctsum format=percent8.1;
	define b / 'b' analysis pctsum format=percent8.1;

	compute b;
	     if b.pctsum > a.pctsum then call define(_col_,'style','style={font_weight=bold background=yellow}');
             if a.pctsum > b.pctsum then call define(2,'style','style={font_weight=bold background=pink}');
	endcomp;

	rbreak after / summarize;
run;

@Cynthia_sas stated that you need to reference the calculated statistic for the comparison. Note that the 2 in the second statement is the column number of the A statistic.

Re: Bolding One Cell Value Based on Comparison with Another in PROC REPORT

Cynthia_sas — Wed, 19 Aug 2020 18:40:01 GMT

Hi:

As I explained in my posting, you MUST use the variable.statistic reference in a COMPUTE block for your reference to any variables. Here are some examples:

A.SUM

B.SUM

A.MEAN

Otherwise, PROC REPORT will generate an error. So if A is an analysis variable and if the statistic for A is PCTSUM -- then the compound name for referencing purpose would need to be A.PCTSUM -- that is the rule I mentioned in my post. And there are other ways you could reference the column, such as indirect reference or macro variable or some other resolved method. But I prefer to show the compound name because that will always work, even if another item gets inserted into the row and the number could change. For example, if the column statement was

column CAT GRP A B, then the number for A would change. but the reference of A.PCTSUM would NOT change, even if the position changed. (As long as the statistic was the same.)

Cynthia