BookmarkSubscribeRSS Feed
rehmanpanjwani
Calcite | Level 5

 

 

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

 

4 REPLIES 4
BrunoMueller
SAS Super FREQ

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
Calcite | Level 5

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

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
Super User

@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.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 1018 views
  • 2 likes
  • 3 in conversation