Traffic Lighting with Proc Tabulate

[rehmanpanjwani]

Hi, Every body. 

The desired result or report is mentioned below. Task in this report is that if score is between ORMC – BRMC threshold then score will be highlighted with green color and if score is between MRC and BRMC threshold then score will be highlighted with blue color and if score is greater than MRC then score color will be highlighted with Red color. I also mentioned below the code which I used to construct below report without incorporating the traffic lighting and also attached the table used in it. 

TITLE;
TITLE1 "KRI Report for the Period Jan - Oct 2017";
FOOTNOTE;
FOOTNOTE1 "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on %TRIM(%QSYSFUNC(DATE(), NLDATE20.)) at %TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
/* -------------------------------------------------------------------
   Code generated by SAS Task

   Generated on: Friday, December 15, 2017 at 4:56:37 PM
   By task: ORMC

   Input Data: WORK.FILTER_FOR_QUERY_FOR_KRI_OBS_DET
   Server:  SASApp
   ------------------------------------------------------------------- */

/* -------------------------------------------------------------------
   Run the tabulate procedure
   ------------------------------------------------------------------- */
PROC TABULATE
DATA=WORK.FILTER_FOR_QUERY_FOR_KRI_OBS_DET(FIRSTOBS=1 )
 
 FORMAT=COMMA20.
 ;
 
 VAR SCORE1;
 CLASS "Key Risk Indicator (KRI)"n / ORDER=UNFORMATTED MISSING;
 CLASS "Reporting Frequency"n / ORDER=UNFORMATTED MISSING;
 CLASS "Responsible Unit"n / ORDER=UNFORMATTED MISSING;
 CLASS "Threshold for ORMC"n / ORDER=UNFORMATTED MISSING;
 CLASS DUE_DT / ORDER=UNFORMATTED MISSING;
 CLASS "Threshold for MRC"n / ORDER=UNFORMATTED MISSING;
 CLASS "Threshold for BRMC"n / ORDER=UNFORMATTED MISSING;
 TABLE
  /* ROW Statement */
  "Responsible Unit"n *"Key Risk Indicator (KRI)"n *"Reporting Frequency"n *"Threshold for ORMC"n *"Threshold for BRMC"n *"Threshold for MRC"n  ,
  /* COLUMN Statement */
  DUE_DT=' '  *(SCORE1=' ' * Max=' ' )   ;
 ;

RUN;
/* -------------------------------------------------------------------
   End of task code.
   ------------------------------------------------------------------- */
RUN; QUIT;
TITLE; FOOTNOTE;

regards,

Rehman Panjwani

[BrunoMueller]

with Proc TABULATE you can not trafficligth for a column, based on values from another column.

You can do this with Proc REPORT, see sample below. Note, that when using ACROSS in Proc REPORT, you have to use special column names to address the appropriate column. The example uses an array to access the across values.

proc report data=sashelp.cars;
  columns type horsepower=hpall_avg origin,  horsePower=hpavg origin, invoice=invsum _dummy;
  define type / group;
  define origin / across;
  define hpall_avg / analysis mean f=commax12.;
  define hpavg / analysis mean f=commax12.;
  define invsum  / analysis n f=commax12.;
  define _dummy / computed;

  compute _dummy / char length=32;
    array nhpavg{*} _c3 _c4_ _c5_;
    do i = 1 to dim(nhpavg);
      if nhpavg{i} > (hpall_avg * 1) then do;
        call define(vname(nhpavg{i}), "style", "style={background=cx4dbf81}");
      end;
    end;
  endcomp;
run;

[rehmanpanjwani]

Dear Bruno,

Thank you for response. Will appreciate to implement this logic in my data. as I could not understand the given coding by you. 

regards,

Rehman Panjwani

[BrunoMueller]

the code sample compares the average horsepower by type with the average horsepower for the crossing of type and origin and colors the cell green.

[Reeza]

---

@rehmanpanjwani wrote:

Dear Bruno,

Thank you for response. Will appreciate to implement this logic in my data. as I could not understand the given coding by you. 

regards,

Rehman Panjwani

---

The code uses SASHELP data set so you can run it in your system. Once it runs, start changing things and see how it behaves. Once you understand that, you can move to implementing it on your data set.