Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- ODS Reports
- /
- how to color excel output

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-25-2017 05:02 PM
(15472 views)

Hi fellow SAS users,

I'm trying to highlight certain values in excel but it looks more difficult that it sounds.

Program-

1. Calculate visit dates and compare it with actual data visit dates.

2. Only output MY calculate dates on excel

3. If actual data dates are missing, then highlight my calculated dates on the excel output.

First of all: how do I color the cells?

Second of all: how do I color specific values through a comparison from calculated vs. actual data?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi:

What code have you tried? You did not post any data, so it is hard to visualize the structure of your data or understand your logic. And, without any data, you make it more difficult for people to help you. But to illustrate how to highlight one data cell based on the value in another data cell on a report, consider this example that uses SASHELP.CLASS. This data does not have any dates, so I used a silly formula that subtracted HEIGHT from WEIGHT and then multiplied by 2. If the integer number returned by that calculation was the same as the integer value of the height, then the new cell CALCVAR2 was assigned the value of the original HEIGHT data cell and also got highlighted with light orange (peachpuff) and also, NAME and HEIGHT got highlighted with the same color.

But, if the integer numbers are not the same, then CALCVAR2 is assigned the value of CALCVAR1 and gets treated differently as far as highlighting. As you can see in Report #1, only the row for Barbara has equivalent integer numbers so it gets the peachpuff highlighting in Report #2 as described. The other rows get purple highlighting in Report #2.

Code and output in Excel are shown below. I used ODS EXCEL, but this logic and highlighting would also work for RTF, PDF, HTML, ODS TAGSETS.EXCELXP, etc...any destination that supports style changes.

cynthia

cell

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi fellow SAS users,

I'm trying to highlight certain values in excel but it looks more difficult that it sounds.

Program-

1. Calculate visit dates and compare it with actual data visit dates.

2. Only output MY calculate dates on excel

3. If actual data dates are missing, then highlight my calculated dates on the excel output.

First of all: how do I color the cells?

Second of all: how do I color specific values through a comparison from calculated vs. actual data?

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi:

What code have you tried? You did not post any data, so it is hard to visualize the structure of your data or understand your logic. And, without any data, you make it more difficult for people to help you. But to illustrate how to highlight one data cell based on the value in another data cell on a report, consider this example that uses SASHELP.CLASS. This data does not have any dates, so I used a silly formula that subtracted HEIGHT from WEIGHT and then multiplied by 2. If the integer number returned by that calculation was the same as the integer value of the height, then the new cell CALCVAR2 was assigned the value of the original HEIGHT data cell and also got highlighted with light orange (peachpuff) and also, NAME and HEIGHT got highlighted with the same color.

But, if the integer numbers are not the same, then CALCVAR2 is assigned the value of CALCVAR1 and gets treated differently as far as highlighting. As you can see in Report #1, only the row for Barbara has equivalent integer numbers so it gets the peachpuff highlighting in Report #2 as described. The other rows get purple highlighting in Report #2.

Code and output in Excel are shown below. I used ODS EXCEL, but this logic and highlighting would also work for RTF, PDF, HTML, ODS TAGSETS.EXCELXP, etc...any destination that supports style changes.

cynthia

cell

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Cynthia,

Thanks for the quick response. I really appreciate your time and knowledge.

I was able to use your code and part of it work. The problem now is to create a second background color "if calculated date > today () then call define (.....{background=gray}');"

summary:

1. create a gold background on calculated visit dates if rawdata visit date is missing.

2. create gray background on calculated visit dates if calculated visit date > today's date.

Thank you.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi, sorry, you did not show ALL your code. I suspect that your else statement is NOT working because you did not know or study the "left-to-right" rule of the COLUMN statement. I would guess that your COLUMN statement has TODAY as the last item in the statement. But this means that if CYCLE_1_DAY_14a appears BEFORE the TODAY item, your logic will not work.

There have been previous postings, with code on this topic. For example, look at Examples 4 and 5 in this posting https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-report-compute-not-returning-desired-resu...

or

https://communities.sas.com/t5/ODS-and-Base-Reporting/Highlight-one-column-with-two-different-colors...

or

https://communities.sas.com/t5/ODS-and-Base-Reporting/quot-Traffic-lighting-quot-premised-on-another...

So, the fix would be to put your TODAY item at the beginning of the COLUMN statement and declare it with a NOPRINT option on the DEFINE statement.

Please look at the previous postings. And, next time, I recommend that you show ALL your code, including ODS statements, not just the middle section without your COLUMN statement or PROC REPORT statement.

cynthia

There have been previous postings, with code on this topic. For example, look at Examples 4 and 5 in this posting https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-report-compute-not-returning-desired-resu...

or

https://communities.sas.com/t5/ODS-and-Base-Reporting/Highlight-one-column-with-two-different-colors...

or

https://communities.sas.com/t5/ODS-and-Base-Reporting/quot-Traffic-lighting-quot-premised-on-another...

So, the fix would be to put your TODAY item at the beginning of the COLUMN statement and declare it with a NOPRINT option on the DEFINE statement.

Please look at the previous postings. And, next time, I recommend that you show ALL your code, including ODS statements, not just the middle section without your COLUMN statement or PROC REPORT statement.

cynthia

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

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.