topic Re: CALL DEFINE - Highlighting cells of the column in ODS and Base Reporting

CALL DEFINE - Highlighting cells of the column

mlogan — Sun, 05 Aug 2018 16:11:43 GMT

Hi All,

I am stuck with highlighting some cells of a column based on a comparison of cells of another column. In the following code I tried to use CALL DEFINE so that it highlights when column _c2_ is bigger than _c3_. Can someone help me please.

PROC SQL;
CREATE TABLE sale AS 
SELECT country, 
	   region, 
       prodtype, 
       product, 
	   actual LABEL=''FORMAT=comma10.2,
	   predict LABEL=''FORMAT=comma10.2,
	   month
FROM sashelp.prdsale 
WHERE mod(monotonic(),75)=0
ORDER BY ranuni(94612);
QUIT;

PROC REPORT DATA=sale NOWINDOWS MISSING HEADLINE HEADSKIP OUT=test;
COLUMN country (region prodtype) , predict;
DEFINE country / GROUP;
DEFINE region / ACROSS;
DEFINE prodtype / ACROSS;
RBREAK AFTER / SUMMARIZE SKIP ol;
COMPUTE region;
    IF _c2_>_c3_.sum THEN CALL DEFINE (_Col_, "style", "style={background=Yellow}");
ENDCOMP;
RUN;

Re: CALL DEFINE - Highlighting cells of the column

PaigeMiller — Sun, 05 Aug 2018 18:21:53 GMT

Sometimes, it is extremely useful to put the data into the form you want it to be and then just use PROC REPORT to actually display the data with appropriate formatting. This is one of those cases, although in general I do it this way. I don't know if you can actually force PROC REPORT to do what you want without pre-processing the data (what do you think, @Cynthia_sas?)

PROC  SQL;
CREATE TABLE sale AS 
SELECT country, 
	   region, 
       prodtype, 
       product, 
	   actual FORMAT=comma10.2,
	   predict FORMAT=comma10.2,
	   month
FROM sashelp.prdsale 
WHERE mod(monotonic(),75)=0
ORDER BY ranuni(94612);
QUIT;

proc summary data=sale nway;
	class country region;
	var predict;
	output out=_sums_ sum=;
run;
data _sums1_;
	merge _sums_(where=(region='EAST') rename=(predict=predict_east)) _sums_(where=(region='WEST') rename=(predict=predict_west));
	by country;
	if predict_east>predict_west then flag=1; else flag=0;
	drop region;
run;
proc report data=_sums1_ nowindows missing headline headskip out=test;
	column country flag ("Predicted Sales" predict_east predict_west);
	define country / group;
	define flag/display noprint;
	define predict_east / display sum "East";
	define predict_west / display sum "West";
	rbreak after / summarize skip ol;
	compute predict_east;
	    if flag=1 then call define (_col_, "style", "style={background=yellow}");
	endcomp;
run;

Re: CALL DEFINE - Highlighting cells of the column

Cynthia_sas — Sun, 05 Aug 2018 18:58:08 GMT

Hi:
Are you getting notes in the log? You do not need the .sum with absolute column numbers. So I would expect you to be seeing this error in the log:
ERROR: The variable type of _C3_.SUM is invalid in this context.
NOTE: The preceding messages refer to the COMPUTE block for REGION.
NOTE: Will not run due to compilation errors.

But I think that there is still going to be a problem with your trafficlighting and your report doesn't see quite right to me. Do you want your headers to be like report #1 or #2 (without highlighting):

Other issues in your code are that you can't use _COL_ in your CALL DEFINE. When you are working with ACROSS items, the convention is that you have to use

CALL DEFINE('_c2_','style','style={background=yellow}');

You have to use the absolute column number. You were making your IF test in the COMPUTE block for REGION, and so in that context, _COL_ would have applied to the REGION column, not to the PREDICT column (_C2_). Also, some of what you show in your code HEADLINE, HEADSKIP, SKIP, OL are all incompatible with your use of STYLE overrides. Those options only work in the LISTING destination and STYLE overrides do NOT work in the LISTING destination.

In this example, the greater value between the 2 values is highlighted:

Pre-computing the values is necessary sometimes, not in this case. But it's still not entirely clear to me what you are trying to achieve.

Cynthia

Re: CALL DEFINE - Highlighting cells of the column

mlogan — Mon, 06 Aug 2018 15:50:35 GMT

Thanks Cynthia, It really helped to identify the issue on my code. I actually wanted to highlight when PRODUCT of Region/East is higher than PRODUCT of Product Type/Furniture. I still can't figure out how to target that compute column to _c2_ so that I can get the

output like attached. Would you please help. Here is my updated code based on the same dataset.

PROC REPORT DATA=sale NOWINDOWS MISSING OUT=test;
COLUMN country (region prodtype) , predict;
DEFINE country / GROUP;
DEFINE region / ACROSS;
DEFINE prodtype / ACROSS;
RBREAK AFTER / SUMMARIZE;
COMPUTE _c2_;
IF _c2_>_c4_ THEN CALL DEFINE (_c2_, 'style', 'style={background=Yellow}');
ENDCOMP;
RUN;

Re: CALL DEFINE - Highlighting cells of the column

Cynthia_sas — Mon, 06 Aug 2018 17:03:42 GMT

Hi:

Your CALL DEFINE syntax did not follow my example:

CALL DEFINE('_c2_','style','style={background=yellow}');

Anything for the first argument in a CALL DEFINE must be quoted unless you are using _COL_ (not appropriate here) or _ROW_ (also not appropriate here).

Your code does not show the correct use of quotes. And you should have an issue with your COMPUTE block -- your code shows COMPUTE _C2_, which I would not expect to work.

I don't understand how you are getting any output to post.

However, in your code, the way you have your COLUMN statement and as shown in your output, column _c2_ is the total for PREDICT value for ALL Products in Region EAST. In your TABLE, the variable _C4_ is the FURNITURE total for ALL regions on a row.

Your example, doesn't make sense to me with your stated goal. Here's what I see in your example:

In your code, you are comparing the sum of all PREDICT values for EAST (_C2_) with the sum of all PREDICT values for FURNITURE. In your post, you said that you "actually wanted to highlight when PRODUCT of Region/East is higher than PRODUCT of Product Type/Furniture." But your analysis variable is PREDICT. So your report does NOT show any PRODUCT values under the EAST region. This is what I find confusing. Did you mean to type PREDICT?

The analysis variable on your report is PREDICT, what is showing in each cell is the PREDICT total. So your COMPUTE block should have

COMPUTE PREDICT;

And, of course, you'd need to correct the CALL DEFINE statement, as I indicated above. If you mean PREDICT instead of PRODUCT in what you want, then my report #1 produced the desired results.

Notice how my COMPUTE block is on the PREDICT variable and my CALL DEFINE shows the correct syntax for argument 1.

Other reports are possible using different nesting. I offer them as examples because I am not clear on why it makes sense to compare the sum of PREDICT for ALL the products for the EAST region with FURNITURE sales for ALL regions.

This shows product types nesting within region:

This shows regions nested within product types:

I did not bother with highlighting because I'm not sure what you'd highlight in these 2 versions with different nestings.

Cynthia

Re: CALL DEFINE - Highlighting cells of the column

mlogan — Tue, 07 Aug 2018 02:02:35 GMT

Hi Cynthia, Thanks for your time explaining all my errors and different possible outputs. I actually need report #1 your have shown. But the other 2 are also helpful that I can use on my project. Thanks again for taking your time to help me out. I guess I should read and practice the very basic of CALL DEFINE one more time.