How to compare last row values with previous row and highlight its columns accordingly?

esko1779 — Mon, 17 Jun 2019 08:55:07 GMT

Hello! My data set looks like this:

data example;
input month $3. push_ups pull_ups dips;
cards;
JAN 10 10 10
FEB 12 15 10
MAR 14 17 8
APR 16 19 11
MAY 20 18 11
JUN 21 17 11
;
run;

title 'Some table' bold;
proc report data=example;
column month (push_ups pull_ups dips);

define month / group order=data 'Month';

define push_ups / display 'Push-ups';
define pull_ups / display 'Pull-ups';
define dips / display 'Dips';
run;

I.e. after each month I measure how many repetitions of each kind of exercise I can do and add results to the table. I want to do more repetitions every month for each exercise, but sometimes I fail. I'd want to highlight the column with green/snow/red depending on result of comparison of my result in last month and previous one. For example, in this data set I'd like to highlight first column with green, second - with red and third - with snow (not to highlight it at all). I guess I can do it using COMPUTE statement, but I know only _RAW_ and _COL_, not _CELL_ parameters (I know also _VAL_, but I can't use it correctly in this situation and compare desired values). Maybe proc report not a right thing to use here, if so, I can use something else instead of it. My version of SAS is 9.4. Can someone help me or give a hint how to overcome this problem? Thanks in advance!

Re: How to compare last row values with previous row and highlight its columns accordingly?

rajdeep — Tue, 13 Aug 2019 09:15:48 GMT

Hi Esko,

1.Just let me know you want to highlight the complete column or the row with colors?

2. You want to highlight based on the previous month comparison.

Yeah, you are correct there are few computation we can do in Proc Report. I think you can have a check with the below links for better understanding related to proc report.

https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/224-2008.pdf

title 'Some table' bold;
proc report data=example;
column month (push_ups pull_ups dips);

define month / group order=data 'Month';

define push_ups / display 'Push-ups';
define pull_ups / display 'Pull-ups';
define dips / display 'Dips';
/*if you want to apply color in complete row and pass the color code or name as per requirement*/
compute push_ups;
CALL DEFINE(_ROW_, "style", "STYLE=[BACKGROUD=HONEYDEW]"); 
endcomp;
/*if you want to apply color in complete column*/
compute pull_ups;
IF pull_ups > 10 then 
CALL DEFINE(_COL_, "style", "STYLE=[BACKGROUD=lightRED]"); 
endcomp;

COMPUTE dips; 
 CALL DEFINE("_c4_", "style","STYLE=[BACKGROUND=lightRED]"); 
endcomp;
run;

Somehow you can code like the above code, if you need the alteration in Rows, columns, Cell level or conditional level.

If any custom report you need which is by default not available with proc Report then I would suggest please prepare the data as per requirement may be with Data step or proc SQL Step, before using the dataset in proc report.

If you can share the output sample format with colors, may be from Excel you can do that then I can attempt to resolve your query completely.

Thanks and Gud luck.

topic How to compare last row values with previous row and highlight its columns accordingly? in SAS Visual Analytics

How to compare last row values with previous row and highlight its columns accordingly?

Re: How to compare last row values with previous row and highlight its columns accordingly?