BookmarkSubscribeRSS Feed
TomKari
Onyx | Level 15

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

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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;

TomKari
Onyx | Level 15

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

Cynthia_sas
SAS Super FREQ

Hi:

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

cynthia


eg_add_code_template.png
TomKari
Onyx | Level 15

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

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
TomKari
Onyx | Level 15

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

Tom

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
  • 6 replies
  • 1191 views
  • 6 likes
  • 3 in conversation