09-21-2017 01:05 PM
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.
09-21-2017 01:08 PM
ODS EXCEL or TAGSETS.EXCELXP if you're not on 9.4
Condifional formatting is possible but you have to define the rules ahead of time.
And a bunch of very specific examples here:
09-21-2017 03:12 PM
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.
09-27-2017 06:28 AM
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.