BookmarkSubscribeRSS Feed
Malleable
Calcite | Level 5

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

 

3 REPLIES 3
Reeza
Super User

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

 

 

 

ballardw
Super User

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.

Malleable
Calcite | Level 5

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

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