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

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

Ronein — Mon, 19 Mar 2018 09:02:10 GMT

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

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

Ksharp — Mon, 19 Mar 2018 13:58:41 GMT

Try Traffic Light . Calling @Cynthia_sas

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

PaigeMiller — Mon, 19 Mar 2018 14:46:43 GMT

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.

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

Cynthia_sas — Mon, 19 Mar 2018 16:54:45 GMT

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

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

Ronein — Tue, 20 Mar 2018 07:59:42 GMT

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?

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

Cynthia_sas — Tue, 20 Mar 2018 13:57:00 GMT

Hi:

This is what I get:

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

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

PaigeMiller — Tue, 20 Mar 2018 14:27:09 GMT

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

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

Prateek_AU — Sat, 09 Oct 2021 19:59:25 GMT

Hi I have been searching all the conditional formatting related articles in proc report but they have not catered my problem. I'm trying to highlight cells based on the value of another cell( i.e. total) and all the values below the total value should be highlighted according to traffic lightining. PS -: I can not hard code the values in formatting as my values in all the cells are changing on daily basis and I don't want to change the values on daily basis in my code.

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

PaigeMiller — Sat, 09 Oct 2021 23:04:04 GMT

@Prateek_AU wrote:
Hi I have been searching all the conditional formatting related articles in proc report but they have not catered my problem. I'm trying to highlight cells based on the value of another cell( i.e. total) and all the values below the total value should be highlighted according to traffic lightining. PS -: I can not hard code the values in formatting as my values in all the cells are changing on daily basis and I don't want to change the values on daily basis in my code.

Please start a new thread, since this is a different problem. In that new thread, explain your problem from scratch.