The SAS Output Delivery System and reporting techniques

Creating an Excel Sheet in second column

Reply
Super Contributor
Posts: 358

Creating an Excel Sheet in second column

Hi All:

This may sound odd, but can I create an Excel Spreadsheet (using Proc Report and the ExcelXP tagset) that has the first column blank?

I'm not just talking about creating a blank column in the spreadsheet, I basically want the entire sheet 'shoved over' to the right by 1 column.

The problem with the approach of just creating an empty column is that I have ACROSS variables and a ton of headings that still appear in the first column. Even if I blank out the heading text, the column headings that the user requires have borders around them and they also still appear.
SAS Super FREQ
Posts: 8,862

Re: Creating an Excel Sheet in second column

Hi...
I believe that by default, the spreadsheet written by ODS with the ExcelXP tagset starts in A1 (unless you have embedded titles turned on). You MAY be able to change the tagset template for ExcelXP in order to have the table start someplace OTHER than A1.

This may be a question for Tech Support, because they have the expertise available to look at the tagset template for ExcelXP and see whether it's easily changed or whether one of the ODS developers needs to consider that as an enhancement for the tagset.

cynthia
Super Contributor
Posts: 358

Re: Creating an Excel Sheet in second column

Thanks (again) Cynthia.

I seem to have an open conversation going with Support due to some of the things I would like the tagset to do. I think they might take some of my ideas seriously and have suggested that they will pay me in stock (very funny).

While I'm very impressed with the options and flexibility of the ExcelXP tagset, some of the things that I would like to see in my resulting spreadsheets, which my clients think are quite logical, are nearly impossible to deliver.

I think I'm just going to have to tell my clients that they are being unreasonable (no a happy thought), or I have to cobble something together the approaches what they want (lots of unpaid overtime).
SAS Super FREQ
Posts: 8,862

Re: Creating an Excel Sheet in second column

Hi!
Well, I would probably NEVER tell a client they were being unreasonable (even if I THOUGHT they were). I'd look on it as an opportunity to educate them on the difference between cosmetics and information.

Just because Excel lets you put a squiggle border arbitrarily in the middle of a table or allows you to color every 3rd cell on every odd line a different color or put a red circle around a number doesn't mean that SAS will do those same things. (Although, by 9.2, different borders around cells will be possible -- but that's not the point.)

The point is that speed of processing, advanced analytics, accuracy and integrity of data, consistent analysis and reporting takes you down one road and that road may not always lead to the cosmetically pretty, manually produced, fiddle-fingered WYSIWYG spreadsheet result that you can produce if you sit there and manually format a report to make it as "pretty" as possible. You can't even THINK about reading 10 years of census data or 2 hours of telecom switch data with Excel, much less analyze it. And that's the tradeoff -- you sacrifice some cosmetics in the interest of getting all that SAS does.

What a lot of my students do is generate their report with ODS HTML or ODS EXCELXP and then apply an Excel macro (or VBscript) to do that last final tweaking -- like starting the whole table in C3 instead of A1 or putting in the squiggle border. Even if you TYPE numbers in Excel, those numbers are plain and unadorned, unless you are typing into an Excel template (.xlt) file. So, even in basic Excel, you have to do -something- above and beyond the basic to get to "pretty".

In my mind, the fallacy with starting from Excel for a target report is that people have come to believe that the "cosmetics" are free and without effort. Because, after all, you just start typing in C3, or you just highlight a cell and click to change the borders of that single cell. How hard is that? Well, not hard at all when a single person is driving the cosmetics for a single spreadsheet.

But, SAS and ODS are not "typing" into your spreadsheet -- they are building cell definitions in a definition language that Excel understands (either HTML or SpreadsheetML XML). So, as hard as it is for folks to understand, I prefer to begin my design with what I can get from ODS by default. You want traffic lighting -- no problem, you want to insert a blank line every 5 rows -- harder, but do-able with a little effort. You want a squiggle double divider line down half the table -- can't be done with SAS (right now). It all comes down to what price the client is willing to pay -- the default from SAS takes X time to produce with Y results, by default; adding on the squiggle line will take Z time to produce and will cost $$$. Do they want to pay the price? Or can they adapt and save money?

It's a hard discussion to have - - the use of WYSIWYG tools has obscured what's really involved in processing data and creating output. Feeling your pain here and bowing my head in a moment of appreciation for what you need to explain to your clients.

cynthia
Ask a Question
Discussion stats
  • 3 replies
  • 131 views
  • 0 likes
  • 2 in conversation