Desktop productivity for business analysts and programmers

Traffic lighting using only Enterprise Guide

Reply
PROC Star
Posts: 1,146

Traffic lighting using only Enterprise Guide

Hi, All

We are working with a group of people who have not learned SAS coding; rather, they are using Enterprise Guide to meet their analysis needs.

As part of their work, they use the "Summary Tables" task to aggregate data. They would like to apply traffic lighting to the summary totals.

Here's an example:
1. Open SASHELP.SHOES into an EG project;
2. Select Describe | Summary Tables;
3. In the Data tab, select Region and Subsidiary as Classification Variables, and Total Sales as Analysis Variable;
4. On the Summary Tables tab, select Subsidiary within Region on the rows, with totals on both, and Sum of Total Sales on the columns.

When we run the task, we would like the totals for the Regions to have the following coloring:

Red if less than 3,000,000;
Yellow if less than 5,000,000, GE 3,000,000
Green if GE 5,000,000

Does anyone know how to do this completely in Enterprise Guide, without using any SAS code?

Thanks,

  Tom

SAS Super FREQ
Posts: 8,820

Re: Traffic lighting using only Enterprise Guide

Tom:

  This is very possible. But, they will at least need a user-defined format and a teeny, tiny bit of code change.

1) basically, you highlight the column or row you want to impact and modify the DATA VALUE properties. First, change the background color of the row variable or column variable to some color like light yellow or light green. This will force EG to put a STYLE= override into the TABULATE code in the right place. Run the task and make sure the column or row is all the right color.
 
2) then use either EG or code to make a user defined format that defines the ranges and the colors you want. Let's say you call this user-defined format TLITE for your numeric variable:
proc format;
  value tlite 0-50 = 'cyan'
              51-60 = 'pink'
              61-high = 'lightgreen';
run;
  
3) CHANGE the code (by Exporting the code or selecting Add as Code Template). So, for example, if you picked light yellow as the background color, you will see this in the TABULATE code:
STYLE={BACKGROUND=#FFFF99}
now you need to make 1 change:
STYLE={BACKGROUND=tlite.}  (the STYLE= option may or may not have another set of curly braces around it. If there are extra curly braces, do NOT change them.

  We teach this in our new EG course on creating reports and graphs (SAS Training in the U.S. -- Creating Reports and Graphs with SAS Enterprise Guide). We show how to make these simple code changes when EG gets you 90% of the way to what you want and you need code for the other 10%.

  So yes, it can be done. But they will need some SAS code. Not a lot, but some. Really, not a lot.

  One hitch in the giddy-up: if you are formatting both a row and a column in TABULATE output, you may run into a "collision" with row totals and/or columns totals. This could happen with either formats or styles. In this case, you might need to add an option to the TABLE statement regarding precedence rules:

TABLE ..... / format_precedence=row or TABLE .... / style_precedence=row ... see example 2 and 3 in the code below

cynthia

Here is a code sample to test. It doesn't have all the bells and whistles and extra stuff that EG adds, but you will get the basic idea. I used more colors.

proc format;

  value tlite low-<100000='pink'

             100000-<500000='verylightpurple'

             500000-<1000000='lightred'

              1000000-<5000000='yellow'

              5000000-high='lightgreen';

run;

  

proc tabulate data=sashelp.shoes f=dollar14.0;

  where region =: 'A';

  class region subsidiary;

  var sales;

  table region*subsidiary,

        sales*sum*{s={background=tlite.}}

    / box='ex 1 no conflict with style';

   

  table region*(subsidiary all*{style={background=lightblue}}) all*{style={background=cyan}},

        sales*sum*{s={background=tlite.}}

     / box='ex 2 no precedence';

   

  table region*(subsidiary all*{style={background=lightblue}}) all*{style={background=cyan}},

        sales*sum*{s={background=tlite.}}

     / box='ex 3 with precedence'  style_precedence=row;

run;

PROC Star
Posts: 1,146

Re: Traffic lighting using only Enterprise Guide

Hi, Cynthia

Great answer...thank you very much! It's not exactly where we wanted to go, but it gets us closer. I'll bet nobody has a better answer (hear that out there; a challenge!).

Incidentally, it appears to me that "Summary Tables" is one of the tasks that can't be added as a task template. If that's not the case, please let me know.

Much nicer using your approach than trying to teach someone how to add the formatting code from scratch.

Thanks,

  Tom

SAS Super FREQ
Posts: 8,820

Re: Traffic lighting using only Enterprise Guide

Hi:

  You can add it as a CODE Template, though. See screenshot.RMB on the task.

cynthia


eg_add_code_template.png
PROC Star
Posts: 1,146

Re: Traffic lighting using only Enterprise Guide

Ah, nifty.

I'm sure someone has pointed this out to me before, but I'd forgotten.

I'm in the habit of just opening the "Code" tab of a task, copying the code, and dropping it into a code window. Any difference in using the Code Template feature?

Thanks,

  Tom

Community Manager
Posts: 2,889

Re: Traffic lighting using only Enterprise Guide

When you right-click on a task and select "Add as a Code Template", that's exactly what it's doing: it creates a new program node and seeds it with the generated SAS code from the task.  It's then a separate item from the original task -- a starting point for your customization.

Chris

PROC Star
Posts: 1,146

Re: Traffic lighting using only Enterprise Guide

Nice feature to know about! Thanks, Cynthia and Chris.

Tom

Ask a Question
Discussion stats
  • 6 replies
  • 393 views
  • 6 likes
  • 3 in conversation