The SAS Output Delivery System and reporting techniques

Conditional formatting

Reply
New Contributor
Posts: 2

Conditional formatting

Hi!

 

I want to create an xml file in SAS (9.4), and I want it to include conditional formatting, so when you change a cell value in that xml file, that cell eg changes colour. I.e. I don’t want to define the cell value in SAS, but in Excel (2016). Is this possible? If so, how?

 

/Felicia

SAS Super FREQ
Posts: 9,365

Re: Conditional formatting

Hi:
You can create output in SAS that includes Excel formulas. But how do you do conditional formatting in Excel? Is it with a formula? If so, then what you describe might work, assuming that you get the formula correctly specified. If there is some mechanism, other than a formula, such as VB or an Excel macro, then I'm not sure you will be able to do what you want.

If you wanted to do conditional formatting in SAS, that would be possible. To find out whether you can even do what you want, you might get the most definitive answer by working with Tech Support.

cynthia
New Contributor
Posts: 2

Re: Conditional formatting

Posted in reply to Cynthia_sas

Thanks Cynthia. I attach an excel file that contains the conditional formatting I want to be produced in SAS. The thought is that the value of A1 is fixed, and the value of B1 can be changed in Excel (and thus change the color of cell A1). I will also try with Tech Support.

/Felicia

SAS Super FREQ
Posts: 9,365

Re: Conditional formatting

[ Edited ]

Hi,
it's really odd, but when I look at your sheet, the value of A1 is red and the value of B1 never changes color when I change the value in A1.

Looking at the Excel file itself, it appears that the formula specifies to change A1, not B1.

xl_cond_fmt.png

However, the question for Tech Support will be whether ODS EXCEL (the only destination that creates XLSX files) can insert the necessary XML into the XLSX zip archive, as shown below.

xl_look_at_xml.png

cynthia

Occasional Contributor
Posts: 5

Re: Conditional formatting

Posted in reply to Cynthia_sas

For this specific problem, I would look at the following paper (or more accurately poster):

http://www2.sas.com/proceedings/sugi31/142-31.pdf

 

It describes how do create an Excel file from SAS with formatted values. 

 

Excel uses the term conditional formatting, but SAS calls it traffic lighting. It appears that traffic lighting has been around for a while. The SUGI 31 paper I mentioned above was written in 2006. I have yet to try it myself with SAS 9.4.

 

 

SAS Super FREQ
Posts: 9,365

Re: Conditional formatting

Traffic lighting has been around for a long time and is easy to do with ODS. However, it is not dynamic. Once ODS makes the background of the cell yellow, for example and the output gets to Excel, then the background of the cell will stay yellow, even if the values in the cell get changed.

If this is what is desired, then the first ways to explore trafficlighting are through the use of PROC FORMAT with PRINT, REPORT and TABULATE. And then for more complex trafficlighting, PROC REPORT uses CALL DEFINE within a COMPUTE block.

Cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 354 views
  • 0 likes
  • 3 in conversation