how to color excel output

[HitmonTran]

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!

[HitmonTran]

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!

[Cynthia_sas · Accepted solution]

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

[HitmonTran]

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.

[Cynthia_sas]

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