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!
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
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!
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
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.
Early bird rate extended! Save $200 when you sign up by March 31.
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.
Ready to level-up your skills? Choose your own adventure.