The SAS Output Delivery System and reporting techniques

how to color excel output

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

how to color excel output

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!


Accepted Solutions
Solution
‎03-26-2017 10:29 PM
SAS Super FREQ
Posts: 8,820

Re: how to color excel output

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

 

hilite_ods_excel.png

 

cell

View solution in original post


All Replies
Occasional Contributor
Posts: 16

How to color excel cells?!

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!


.png
Solution
‎03-26-2017 10:29 PM
SAS Super FREQ
Posts: 8,820

Re: how to color excel output

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

 

hilite_ods_excel.png

 

cell

Occasional Contributor
Posts: 16

Re: how to color excel output

[ Edited ]

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.

 

code.png


code.png
SAS Super FREQ
Posts: 8,820

Re: how to color excel output

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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 512 views
  • 3 likes
  • 2 in conversation