BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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

8 REPLIES 8
Cynthia_sas
SAS Super FREQ
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
PaigeMiller
Diamond | Level 26

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
Ronein
Meteorite | Level 14

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?

Cynthia_sas
SAS Super FREQ

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

PaigeMiller
Diamond | Level 26

@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
Prateek_AU
Calcite | Level 5
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.
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 4113 views
  • 0 likes
  • 5 in conversation