An Idea Exchange for SAS software and services

Comments
by Super User
on ‎04-14-2015 08:10 PM

Can you clarify your issues with the present methods available for this in the above procedures?

by Occasional Contributor RobP
on ‎04-15-2015 10:55 AM

@Reeza Certainly.  The only way to currently do this (within SAS) is through adding lengthy custom (and complex) code prior to your proc to calculate percentiles (or whatever method you are using to color code) and building custom formats, figuring out color gradients, adding a bunch of call define statements to proc report or similar.  Basically it's a huge pain.  And that's if you know what you're doing....

To do all this in Excel, it's as simple as highlighting the data and pressing a button, I don't see why it should be any more complicated than choosing it as an option for whatever PROC you are working with in SAS. 

The minimum SAS needs to know is whether to apply it to the output table or not.  So it should simply be an option.

by Super User
on ‎04-15-2015 11:06 AM

Have you ever applied conditional formatting to 10,000 tables with manual highlight and press button? You would find the programmatic approach MUCH shorter in the long term. I do prepare reports that involve hundreds of tables with traffic highlighting of different ranges of values and developed a number of helper programs to work with my data to facilitate some of that.

I will admit that there are some things that might be nice to have in other than Procs Report, Tabulate and Print but it is not that much of an issue to get the output from one procedure to display using one of the above report procs.

And I'm leery of any automagic different behavior for "outliers". One persons outlier is another persons golden find.

by Occasional Contributor RobP
on ‎04-15-2015 11:53 AM

@ballardw No I admit I haven't done that.  But I guess I wasn't clear in my request.  I'm not asking for a 'button to press' in SAS. I'm asking for it to be an option on the PROCs so that you can use it in a programmatic approach.  And if you wanted to use it to conditionally format 10k tables, then you could if that's what your heart desires. 

Also, I consider conditional formatting to be a more generic form of traffic-lighting.  Traffic lighting generally involves user-defined cutoff's. Conditional formatting is typically algorithm based.  That's how I see it anyway.  The existing call define features for traffic lighting are fine and I'm not proposing any changes to those.

The outliers feature would be an option - not mandatory.  I ask because it's one of the 'gotchas' that I've stumbled across when building my own conditional formatters.  People can choose whether or not they want the outlier-option option on (and perhaps even specify a threshold).  The way we implemented it was by choosing a threshold (how many std deviations away from the mean a number was - obviously it only works if the data is normally distributed).  It allowed us to have the formatting we needed on the cells we were interested in.  Outliers were being caused by low volumes in certain cells and this allowed us to accommodate that.

Even you said that you needed to develop helper programs to get your formatting working.  This is partly my point.  We shouldn't have to develop these helper programs because applying *generic* conditional formatting to a table requires no understanding of the table or the data behind it.

by Occasional Contributor RobP
on ‎04-15-2015 12:25 PM

Below is some example code to illustrate the point I'm trying to make:

* CREATE SAMPLE DATA;
proc sql noprint;
create table sales as
select year,
substr(quarter,5,2) as quarter,
sum(quantity) as qty
from sashelp.orsales
where product_line = 'Children'
group by 1,2
order by 1,2
;
quit;

proc transpose data=sales out=trans (drop=_name_);
by year;
id quarter;
var qty;
run;

The data will look like this:

YearQ1 Q2 Q3 Q4
19994423886983796942
200046379183103088146
20013787831187597284
20024183101551144211213

Now consider the amount of work that would be required to conditionally format that output.  I'm proposing that features (along the lines of below) are added:

**
** PROPOSED APPROACH - EXAMPLE 1 - CONDITIONAL FORMAT EVERY COLUMN BEGINNING WITH "Q" TOGETHER
*;
proc print data=trans;
cformat q:;
run;

**
** PROPOSED APPROACH - EXAMPLE 2 - CONDITIONAL FORMAT EACH COLUMN INDIVIDUALLY
*;
proc print data=trans;
cformat q1;
cformat q2;
cformat q3;
cformat q4;
run;

EDIT: The 2 proposed examples would give results like the below.  I've used heavy borders on the below image to represent the selection I had when I applied the conditional formatting.  On the top table, you can see that all the quarter columns had been selected at the same time.  On the bottom table, you can see that I applied conditional formatting individually to each column.

Untitled.png

by PROC Star
on ‎04-15-2015 03:40 PM

I'm still not understanding what you mean by conditional formatting.  For the sample data and pseudocode you posted, what would be the desired output from the two proc print steps?

by Occasional Contributor RobP
on ‎04-15-2015 03:50 PM

@Quentin Updated to show how each of the proposed examples would differ. 

by Super User
on ‎04-15-2015 03:52 PM

But how do you define cformat, the colours and cutoffs?

by Occasional Contributor RobP
on ‎04-15-2015 04:27 PM

@ Reeza.  I'd leave the actual implementation up to the SAS devs.  If it was me I'd enable further customization along the lines of something like this:

  proc print data=trans;

    cformat q1 / colors=(scheme=HTMLBlue inverse=true); * USE WHATEVER COLORS ARE DEFINED IN THE HTMLBLUE CSS SCHEME.  INVERT THE COLORS SO THAT BLUE IS LOW AND WHITE IS HIGH;

    cformat q2 / colors=(start=#FFFFFF end=#000000 mid=#4F004F);   * CHOOSE YOUR OWN START/END/MID COLORS. MID IS OPTIONAL IF YOU WANT A DUAL COLOR SCHEME;

    cformat q3 ;  * DEFAULT BEHAVIOUR FOR Q3 AS SHOWN IN Q3 ABOVE EXAMPLE;

    cformat q4 / outlierscalc=exclude outlierscolor=true outliersthresh=3 outliermethod=std;  *USES PREDEFINED COLOR SCHEME.  SPECIFY THAT OUTLIERS SHOULD BE EXCLUDED WHEN CALCULATING PERCENTILES. THE THRESHOLD FOR AN OUTLIER WILL BE 3 TIMES THE STANDARD DEV.  OUTLIERS WILL STILL BE COLORED BUT WILL RECEIVE THE MAX OR MIN COLOR.

  run;

by Valued Guide
on ‎04-17-2015 12:52 PM

There is already a lot possible "traffic lighting sas" http://support.sas.com/resources/papers/proceedings11/290-2011.pdf  what is your issue?

Interactive editing is replaced by coding that is needed.

Idea Statuses
Top Liked Authors