topic SAS into Excel with top 3/bottom 3 conditionally formatted/highlighted/colored font? in ODS and Base Reporting

SAS into Excel with top 3/bottom 3 conditionally formatted/highlighted/colored font?

Malleable — Thu, 21 Sep 2017 17:05:30 GMT

I have a dataset with variables 'state', 'industry', 'year', 'amount' and 'growth' (year to year percent increase in the 'amount' variable).

I want to determine the top 3 and bottom three 'amount' and 'growth'; by 'state', 'industry' and 'year'.

I want to conditionally format the 'amount' and 'growth' values with green font if its a top 3 value, and red font if its a bottom three value.

Then export it to an Excel worksheet - with the red/green conditional formatting intact.

What's the easiest way to do this? If the red/green formatting can't be maintained when exporting to Excel, is there a way to do this with SAS reports. Formatting once its in Excel seems impossible given my 500k+ obs. I have very little experience with SAS reports.

I am using SAS 9.4.

Thanks,

Chris

Re: SAS into Excel with top 3/bottom 3 conditionally formatted/highlighted/colored font?

Reeza — Thu, 21 Sep 2017 17:08:32 GMT

ODS EXCEL or TAGSETS.EXCELXP if you're not on 9.4

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

Condifional formatting is possible but you have to define the rules ahead of time.

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

And a bunch of very specific examples here:

http://support.sas.com/resources/papers/proceedings13/366-2013.pdf

Re: SAS into Excel with top 3/bottom 3 conditionally formatted/highlighted/colored font?

ballardw — Thu, 21 Sep 2017 19:12:51 GMT

Are you sending all 500K records to Excel?

If so, who actually reads 500k line reports? In old fashion 80 line per page output that would be more that 6250 pages or a good chunk of an encyclopedia.

I think we may be missing a bit of the puzzle.

Re: SAS into Excel with top 3/bottom 3 conditionally formatted/highlighted/colored font?

Malleable — Wed, 27 Sep 2017 10:28:15 GMT

Thanks for the reply guys. I will attempt the above solutions.

Yes, I need to export everything to Excel. The half the group reviewing the estimates work in programs besides SAS, so for review purposes we are all required to submit in Excel.

A typical meeting might have people filtering by state/year/industry, making calculations on the fly, or throwing up Pivot charts from the Excel sheet - but I need to have the initial conditional formatting available.

Chris