BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bob021
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

12 REPLIES 12
PGStats
Opal | Level 21

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
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6

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;

PGStats
Opal | Level 21

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;

Capture.PNG

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

PG

PG
bob021
Calcite | Level 5

Thank you very much PG Stats and Qli.

Working like a charm.

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

Bob

PGStats
Opal | Level 21

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;

Capture2.PNG


PG

PG
bob021
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ


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;

PGStats
Opal | Level 21

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
Cynthia_sas
SAS Super FREQ

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

cynthia

PGStats
Opal | Level 21

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
Cynthia_sas
SAS Super FREQ


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

bob021
Calcite | Level 5

Hi Cynthia, PG

Thank you very much.

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

Thank you again.

Bob

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1807 views
  • 7 likes
  • 4 in conversation