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;
Thank you in advance
Bob
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
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
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;
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
Thank you very much PG Stats and Qli.
Working like a charm.
Is it possible to make the comparison to a moving target?
Bob
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
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
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;
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
Ah, I misunderstood the requirement. I thought that target was fixed and the calculated mean was the point of comparison. Thanks for clarifying.
cynthia
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
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
Hi Cynthia, PG
Thank you very much.
Solution is perfect. It is time for me to learn proc report.
Thank you again.
Bob
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.