Desktop productivity for business analysts and programmers

Traffic Lighting with Proc Tabulate

Reply
New Contributor
Posts: 3

Traffic Lighting with Proc Tabulate

 

 

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. 

 

KRI Report.JPG

 

 

 

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;

KRI Report.JPG

 

 

regards,

Rehman Panjwani

 

SAS Super FREQ
Posts: 831

Re: Traffic Lighting with Proc Tabulate

Posted in reply to rehmanpanjwani

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;

 

New Contributor
Posts: 3

Re: Traffic Lighting with Proc Tabulate

Posted in reply to Bruno_SAS

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

SAS Super FREQ
Posts: 831

Re: Traffic Lighting with Proc Tabulate

Posted in reply to rehmanpanjwani

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.

Super User
Posts: 24,026

Re: Traffic Lighting with Proc Tabulate

Posted in reply to rehmanpanjwani

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.

Ask a Question
Discussion stats
  • 4 replies
  • 203 views
  • 2 likes
  • 3 in conversation