topic Re: How to add column average on the end of table (proc tabulate)? in SAS Procedures

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

bob021 — Mon, 25 Jun 2012 13:47:44 GMT

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 b 5 1

alfa a 5 2

alfa b 5 1

alfa b 5 3

alfa a 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;

Thank you in advance

Bob

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

PGStats — Mon, 25 Jun 2012 16:43:30 GMT

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;

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

QLi — Mon, 25 Jun 2012 20:00:14 GMT

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;

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

PGStats — Tue, 26 Jun 2012 01:58:18 GMT

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.

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

bob021 — Tue, 26 Jun 2012 06:31:23 GMT

Thank you very much PG Stats and Qli.

Working like a charm.

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

Bob

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

PGStats — Wed, 27 Jun 2012 02:50:40 GMT

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;

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

bob021 — Wed, 27 Jun 2012 12:28:02 GMT

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;

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()];

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

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

Cynthia_sas — Wed, 27 Jun 2012 14:39:05 GMT

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;

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

PGStats — Wed, 27 Jun 2012 14:53:37 GMT

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.

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

Cynthia_sas — Wed, 27 Jun 2012 15:35:35 GMT

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

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

PGStats — Wed, 27 Jun 2012 19:42:28 GMT

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.

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

Cynthia_sas — Wed, 27 Jun 2012 21:22:17 GMT

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

cynthia

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

bob021 — Thu, 28 Jun 2012 07:15:50 GMT

Hi Cynthia, PG

Thank you very much.

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

Thank you again.

Bob