topic Re: GET Maximum values on a row should be highlighted in sas Proc Report in New SAS User

GET Maximum values on a row should be highlighted in sas Proc Report

sivastat08 — Fri, 21 Jun 2019 11:52:22 GMT

Hi Get Maximum values on a row should be highlighted in SAS PROC Report,

Below is the code i have tried but its not working for me. Kindly help on getting maximum of (MAX_of_Invoice)

row should be highlighted in PROC report

PROC SQL INOBS=50;
CREATE TABLE WORK.QUERY_FOR_CARS1 AS
SELECT
t1.Make,
t1.Invoice
FROM SASHELP.CARS t1;
QUIT;

proc sort data=QUERY_FOR_CARS1 out=QUERY_FOR_CARS2;
by Make Invoice;
run;

data QUERY_FOR_CARS3;
set QUERY_FOR_CARS2;
by Make Invoice;
Seq+1;
if first.Make then Seq=1;
run;
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_CARS3_0000 AS
SELECT t1.Make,
/* MAX_of_Invoice */
(MAX(t1.Invoice)) FORMAT=DOLLAR8. AS MAX_of_Invoice
FROM WORK.QUERY_FOR_CARS3 t1
GROUP BY t1.Make;
QUIT;
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_CARS3_0001 AS
SELECT t1.Make,
t1.Invoice,
t1.Seq,
t2.MAX_of_Invoice
FROM WORK.QUERY_FOR_CARS3 t1
LEFT JOIN WORK.QUERY_FOR_CARS3_0000 t2 ON (t1.Make = t2.Make);
QUIT;

proc report data=work.QUERY_FOR_CARS3_0001;
title HEIGHT=.25in 'Testing Max Vol' bold;
column Make Invoice Seq MAX_of_Invoice;
define Make/center;
define Invoice/center;
/* define Seq/center;*/
define MAX_of_Invoice/center display ;
compute Invoice;

if _C2_ = _C4_ then
call define(_row_,"style","style={background=red}");
endcomp;
run;

Re: GET Maximum values on a row should be highlighted in sas Proc Report

Cynthia_sas — Sat, 22 Jun 2019 18:44:14 GMT

Hi:

I'm not sure why you are using ABSOLUTE COLUMN NUMBERs here... they are not appropriate since you don't have any ACROSS items. Your real problem is that your COLUMN statement shows INVOICE as appearing BEFORE MAX_OF_INVOICE in the COLUMN statement. That means:

In a COMPUTE block for INVOICE, PROC REPORT only knows the value of MAKE. It does NOT yet know the value of SEQ or MAX_OF_INVOICE -- because PROC REPORT works from left-to-right in placing items on the report row. So a variable in a COLUMN statement can only test a value that appears BEFORE it on the COLUMN statement -- as shown above. SEQ and MAX_OF_INVOICE are not known to PROC REPORT in the COMPUTE block for INVOICE.

I also don't know why you have or need so many PROC SQL steps. I'm also not sure of the purpose of the SEQ variable. You probably don't want MAKE to be a usage of GROUP, I would guess you mean for it to be a usage of ORDER and maybe you are forcing SEQ to show every row because with a usage of GROUP for MAKE, all the numeric rows would collapse down to 1 row for each make and be summarized.

However, you're sorting the SASHELP.CARS by MAKE and INVOICE -- which means that last value for MAKE will always be the MAX value. There are really 2 possible solutions without all the SQL steps (but keeping the DATA step so you get SEQ -- and one of my solutions uses a macro variable for each MAKE value to hold the max amount for that MAKE.)

Here's how I made the data:

Then here's report 1 using the CARS2 data:

And here is alternate approach 2 using the CARS2 data and the macro variables from the DATA step program:

If you want to hide the column for MAKE that shows the ORDER usage, then just remove the comment delimiters from around the NOPRINT option.

Hope this gives you some other ideas for a solution.

Cynthia

Re: GET Maximum values on a row should be highlighted in sas Proc Report

sivastat08 — Tue, 25 Jun 2019 10:07:00 GMT

Thanks for answer.