BookmarkSubscribeRSS Feed
tjofen
Calcite | Level 5

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

5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
tjofen
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

sugiyama
Fluorite | Level 6

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.

 

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 5 replies
  • 1857 views
  • 0 likes
  • 3 in conversation