DATA Step, Macro, Functions and more

conditional formatting of color scales (red-yellow-green colors) in SAS

Reply
Frequent Contributor
Posts: 91

conditional formatting of color scales (red-yellow-green colors) in SAS

Hello

I have SAS that was created by export from SAS to excel and then use Excel formats.

In my work I have a task to create formats in SAS (Proc Report) and then export the fornmatted report to excel.

In excel I have one column with conditional formatting of color scales (red-yellow-green colors).

Does anyone know how to create conditional formatting of color scales (red-yellow-green colors) in SAS?

 

Thanks

Ronein

Super User
Posts: 10,610

Re: conditional formatting of color scales (red-yellow-green colors) in SAS

Try Traffic Light . Calling @Cynthia_sas

SAS Super FREQ
Posts: 9,253

Re: conditional formatting of color scales (red-yellow-green colors) in SAS

There have been so many postings already on trafficlighting using PROC REPORT. All it should take is a search in the Communities to find them.
Cynthia
Respected Advisor
Posts: 2,647

Re: conditional formatting of color scales (red-yellow-green colors) in SAS

[ Edited ]

Let's suppose you can identify some threshold values for the reds, yellows and greens. For example, 0-0.5 is green, 0.5 to 1 is yellow and above 1 is red.

 

Then, for example:

 

proc format;
    value ryg = 0-<0.5=lightmoderategreen 0.5-<1=yellow 1-high=lightmoderatered;
run;

proc report data=have;
    columns a b c d e;
    define b/style={background=ryg.}; /* Repeat as needed for other columns */
run;

If you really want, you can have SAS compute percentiles for your variables and then use the percentiles as cutoffs; this is a little more involved and requires either macros or CALL EXECUTE.

--
Paige Miller
Frequent Contributor
Posts: 91

Re: conditional formatting of color scales (red-yellow-green colors) in SAS

Posted in reply to PaigeMiller

Thanks a lot for your reply.

I am not sure how conditional formatting--Color Scales--Red Yellow Green color scales is working in excel .

My target is to get same colors formats in SAS like in Excel.

Do you know how to do it please?

SAS Super FREQ
Posts: 9,253

Re: conditional formatting of color scales (red-yellow-green colors) in SAS

Hi:

  This is what I get:

tlite.png

So, the traffic light method does work in ODS EXCEL (and other destinations. SAS can use any color you specify, as long as you specify it using the RGB color system, the HLS color system or using the registry names. I do not understand what you mean when you say "My target is to get same colors formats in SAS like in Excel."  Do you mean you want the exact same color choices that you have in Excel? It should be pretty easy to figure out those color values if you mean you want the same color choices. Otherwise, I don't know what you mean. The only other way to do traffic lighting with PROC REPORT is to use a CALL DEFINE, which isn't needed in this example.

 

Cynthia

Respected Advisor
Posts: 2,647

Re: conditional formatting of color scales (red-yellow-green colors) in SAS


@Ronein wrote:

Thanks a lot for your reply.

I am not sure how conditional formatting--Color Scales--Red Yellow Green color scales is working in excel .

My target is to get same colors formats in SAS like in Excel.

Do you know how to do it please?


If the request is to match Excel colors exactly, this seems like a huge amount of work. Excel has a scaling algorithm that will take the data value in each cell and turn it into a color in the red-yellow-green spectrum. If you just want to get close, so that when Excel uses one of the darker greens, SAS uses one of the darker greens but not necessarily the exact same dark green as Excel, that seems much more doable.

--
Paige Miller
Ask a Question
Discussion stats
  • 6 replies
  • 102 views
  • 0 likes
  • 4 in conversation