BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HitmonTran
Pyrite | Level 9

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
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
HitmonTran
Pyrite | Level 9

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
Cynthia_sas
SAS Super FREQ

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

HitmonTran
Pyrite | Level 9

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
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Available on demand!

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

 

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
  • 4 replies
  • 15473 views
  • 3 likes
  • 2 in conversation