The SAS Output Delivery System and reporting techniques

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

Reply
New Contributor
Posts: 4

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

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

 

Super User
Posts: 22,875

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

Posted in reply to Malleable

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

 

 

 

Super User
Posts: 13,084

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

Posted in reply to Malleable

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.

New Contributor
Posts: 4

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 128 views
  • 0 likes
  • 3 in conversation