Solved
Contributor
Posts: 60

# How to add column average on the end of table (proc tabulate)?

HI to All

1. How to add column average on the end of table?  (average result in first line (4+5+6) /3 = 5)

2. How to exclude from average if  N = 0?          (average result in second line (6+0+6) /2 = 6)

3. Is it possible to change background of cell if target > N?

data process;

input

process \$ id \$ target \$ day \$;

datalines;

alfa a 5 1

alfa a 5 1

alfa a 5 1

alfa a 5 1

alfa b 5 1

alfa b 5 1

alfa b 5 1

alfa b 5 1

alfa a 5 2

alfa a 5 2

alfa a 5 2

alfa a 5 2

alfa a 5 2

alfa b 5 1

alfa b 5 1

alfa b 5 3

alfa b 5 3

alfa a 5 3

alfa a 5 3

alfa a 5 3

alfa a 5 3

alfa a 5 3

alfa b 5 3

alfa b 5 3

alfa b 5 3

alfa b 5 3

alfa a 5 3

;

run;

ods listing close;

ods html body = 'f:\export.html' ;

run;

proc tabulate data=process;

class process id target day;

table process = '' * id = ''* target = '' , day = ''

all='Total'

/ misstext='0';

run;

ods html close;

ods listing;

run;

Bob

Accepted Solutions
Solution
‎06-25-2012 12:43 PM
Posts: 5,539

## Re: How to add column average on the end of table (proc tabulate)?

A few steps toward the solution :

data process;
input
process \$ id \$ target \$ day \$;
datalines;
alfa a 5 1
alfa a 5 1
alfa a 5 1
alfa a 5 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa b 5 1
alfa b 5 1
alfa b 5 3
alfa b 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa a 5 3
;

proc sql;
create view processN as
select process, id, target, day, count(*) as nb
from process
group by process, id, target, day;
quit;

ods listing close;
ods html body = "&SASForum\export.html";

proc tabulate data=processN format=4.0;

class process id target day;
var nb;
table process = '' * id = ''* target = '' ,

(day = 'N' all='Mean N')*nb=""*mean=""
/ misstext='0';

run;

ods html close;
ods listing;

PG

PG

All Replies
Solution
‎06-25-2012 12:43 PM
Posts: 5,539

## Re: How to add column average on the end of table (proc tabulate)?

A few steps toward the solution :

data process;
input
process \$ id \$ target \$ day \$;
datalines;
alfa a 5 1
alfa a 5 1
alfa a 5 1
alfa a 5 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa b 5 1
alfa b 5 1
alfa b 5 3
alfa b 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa a 5 3
;

proc sql;
create view processN as
select process, id, target, day, count(*) as nb
from process
group by process, id, target, day;
quit;

ods listing close;
ods html body = "&SASForum\export.html";

proc tabulate data=processN format=4.0;

class process id target day;
var nb;
table process = '' * id = ''* target = '' ,

(day = 'N' all='Mean N')*nb=""*mean=""
/ misstext='0';

run;

ods html close;
ods listing;

PG

PG
Contributor
Posts: 59

## Re: How to add column average on the end of table (proc tabulate)?

3. Is it possible to change background of cell if target > N?

Make a little bit change based on the code of PG:

/* Change Target Type from Char to Num*/
proc sql;

create view processN as
select process, id, input(target,2.)as target_n, day, count(*) as nb
from process
group by 1, 2, 3, 4;
quit;

/* Color Format*/

proc format ;
value  col low -5 ='yellow'
6-high='red';
run;

proc tabulate data=processN format=4.0;

class process id target_n day;
classlev target_n /s=[background=col.];
var nb;
table process = '' * id = ''* target_n = '' ,

(day = 'N' all='Mean N')*nb=""*mean=""
/ misstext='0';

run;

Posts: 5,539

## Re: How to add column average on the end of table (proc tabulate)?

Thanks QLi ! I didn't know about color formats, neat! How about leaving the SQL as is, and changing :

/* Color Format*/

proc format ;
value col
other  = 'white'
. = "verylightyellow"
low-<5 = 'verylightred';
run;

ods listing close;
ods html body = "&SASForum\export.html" ;

proc tabulate data=processN format=4.0;

class process id target day;
var nb;
table process = '' * id = ''* target = '' ,
(day = 'N' all='Mean N')*nb=""*mean=""*[style=[background=col.]]
/ misstext='0' ;
run;

ods html close;
ods listing;

Of course, it always compares to target=5. I wouldn't know how to make the comparison to a moving target.

PG

PG
Contributor
Posts: 60

## Re: How to add column average on the end of table (proc tabulate)?

Thank you very much PG Stats and Qli.

Working like a charm.

Is it possible to make the comparison to a moving target?

Bob

Posts: 5,539

## Re: How to add column average on the end of table (proc tabulate)?

You might be better with more sophisticated tools like proc report or template language. But a challenge is a challenge. I found a nasty way for setting the background color as a function of the target: hide the target in the decimal part of the counts and define a format baed on a function. Here is how it goes (I modified the example data to show the effect) :

data process;
input
process \$ id \$ target day \$;
datalines;
alfa a 6 1
alfa a 6 1
alfa a 6 1
alfa a 6 1
alfa a 6 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa b 5 1
alfa b 5 1
alfa b 5 3
alfa b 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa a 5 3
;

/* Hide the target as decimals within the count (nb) */
proc sql;
create view processN as
select process, id, target, day, count(*) + 0.0001*target as nb
from process
group by process, id, target, day;
quit;

/* Define a color format that interprets the decimals as the target */
proc fcmp outlib=sasuser.fcmp.format;
function bTarget(x) \$16;

if missing(x) then return ("verylightyellow");
else if int(x) < round(10000*(x-int(x))) then return ("verylightred");
else return ("white");
endsub;
run;
options cmplib=(sasuser.fcmp);

proc format ;
value col
other  =  [bTarget()];
run;

/* call the format for the cells created by proc tabulate */
ods listing close;
ods html body = "consultations\SAS Community\export.html" ;
proc tabulate data=processN format=4.0;

class process id target day;
var nb;
table process = '' * id = ''* target = '' ,
(day = 'N'*[style=[background=col.]] all='Mean N')*nb=""*mean=""
/ misstext='0' ;

run;
ods html close;
ods listing;

PG

PG
Contributor
Posts: 60

## Re: How to add column average on the end of table (proc tabulate)?

Thank you very much PG Stats.

There is a syntax error  and i think the problem is in my version of SAS 9.1

46   /* Define a color format that interprets the decimals as the target */

47   proc fcmp outlib=sasuser.fcmp.format;

48        function bTarget(x) \$16;

49

50             if missing(x) then return ("verylightyellow");

------------------

22               200

51             else if int(x) < round(10000*(x-int(x))) then return ("verylightred");

---------------

22            200

52             else return ("white");

--------

22     200

ERROR 22-322: Syntax error, expecting one of the following: a name, a numeric constant,

a datetime constant, a missing value, (, +, -, IF.

ERROR 200-322: The symbol is not recognized and will be ignored.

53        endsub;

54   run;

and

proc format ;

59   value col

60   other  =  [bTarget()];

-

22

-

200

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,

a datetime constant, a missing value, ;, LOW, OTHER.

ERROR 200-322: The symbol is not recognized and will be ignored.

Thak you again.

Bob

SAS Super FREQ
Posts: 9,371

## Re: How to add column average on the end of table (proc tabulate)?

Hi:

Using a function to set a format label became available in SAS 9.3, I believe. You might want to check with Tech Support to be sure.

Otherwise, if you switched to PROC REPORT, you could use a CALL DEFINE statement in a COMPUTE block to do what you want. The code is a bit more verbose, but you would NOT need your SQL step at all.

cynthia

data process;
input process \$ id \$ target \$ day \$;
datalines;
alfa a 5 1
alfa a 5 1
alfa a 5 1
alfa a 5 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa b 5 1
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa a 5 2
alfa b 5 1
alfa b 5 1
alfa b 5 3
alfa b 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa a 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa b 5 3
alfa a 5 3
;
run;

ods listing close;
ods html body = "c:\temp\use_report.html";

options missing = '0';

proc report data=process nowd;
column  process  id  target   n,day  calcmean;
define process /'Process' group;
define id / 'ID' group;
define target / 'Target' group;
define day / 'Day' across;
define n / ' ';
define calcmean / computed 'Mean N';
compute calcmean;
** calcmean will be generated for every report row;
** then every cell for DAY will be compared to the calculated mean;
** _c4_ is the absolute report column # for DAY=1;
** _c5_ is the absolute report column # for DAY=2;
** _c6_ is the absolute report column # for DAY=3;
** mean function ignores missing;
calcmean = (mean(_c4_, _c5_, _c6_));

** if no color is set here, the default background for the style will be used;
if _c4_ ge 1 and _c4_ lt calcmean then do;
call define('_c4_','style','style={background=verylightred}');
end;
else if _c4_ = . then do;
call define('_c4_','style','style={background=verylightyellow}');
end;

if _c5_ ge 1 and _c5_ lt calcmean then do;
call define('_c5_','style','style={background=verylightred}');
end;
else if _c5_ = . then do;
call define('_c5_','style','style={background=verylightyellow}');
end;

if _c6_ ge 1 and _c6_ lt calcmean then do;
call define('_c6_','style','style={background=verylightred}');
end;
else if _c6_ = . then do;
call define('_c6_','style','style={background=verylightyellow}');
end;
endcomp;
run;

ods html close;
ods listing;

Posts: 5,539

## Re: How to add column average on the end of table (proc tabulate)?

Thanks Cynthia for coming to the rescue! I've got to learn the proc report way, some day...

Bob, a little testing convinced me that you will be getting the proper trafficlighting with Cynthia's code by changing the references to calcmean in the comparisons to target.

PG

PG
SAS Super FREQ
Posts: 9,371

## Re: How to add column average on the end of table (proc tabulate)?

Ah, I misunderstood the requirement. I thought that target was fixed and the calculated mean was the point of comparison. Thanks for clarifying.

cynthia

Posts: 5,539

## Re: How to add column average on the end of table (proc tabulate)?

You must be right Bob. I did my tests with SAS 9.3. Anyway, it is not a technique that I recommend but an opportunity to explore and demonstrate new features. Maybe some proc report expert can guide you to a better way to produce your report.

Good luck.

PG

PG
SAS Super FREQ
Posts: 9,371

## Re: How to add column average on the end of table (proc tabulate)?

Hi, PG:

I thought your solution was very clever, hiding the number in the far decimal places. I posted a PROC REPORT solution -- more verbose than your solution, but I didn't need the SQL step and it used REPORT and CALL DEFINE for the trafficlighting.

cynthia

Contributor
Posts: 60

## Re: How to add column average on the end of table (proc tabulate)?

Hi Cynthia, PG

Thank you very much.

Solution is perfect. It is time for me to learn proc report.

Thank you again.

Bob

🔒 This topic is solved and locked.