Date: Thu, 1 Oct 2009 19:16:51 -0700
Reply-To: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject: Re: ODS tagsets.excelxp: What is possible and what not?
In-Reply-To: <1c32b696-08d9-4cb9-a90e-7f5a0e0b9d32@x37g2000yqj.googlegroups.com>
Content-Type: text/plain; charset=iso-8859-1; format=flowed; delsp=yes
You can do most of that, but in some cases you'll have to create a
sheet yourself instead of having SAS do it for you.
--
Jack Hamilton
jfh@alumni.stanford.org
Tots units fem força!
On Oct 1, 2009, at 8:36 am, Alex wrote:
> Hi everyone,
>
> Today I looked into ODS markup for the first time and tried to figure
> out whether I could fulfill my current task with ODS tagsets.excelxp.
> I read through the help text that I generated with "ods
> tagsets.excelxp options ( doc = 'help' ) ;" and my impression is that
> I can't fulfill my requirements like this. My alternative to using ODS
> would be to create a basic xls workbook without the fancy stuff
> described below and then format this using VB.Net or C#. But I would
> much prefer to solve this in SAS.
>
> Here's what I need to do:
> - Create one Excel workbook containing dozens of sheets, each one
> containing the cells from a SAS data set
OK
> - Create table of contents sheets for subsets of data sets which
> belong together. These special sheets must contain one row per sheet
> belonging to the subset, a hyperlink to every sheet of the subset, and
> the row count for every sheet. It should look more or less like this:
> SHEET_NAME, ROW_COUNT, LINK, ... <more columns>
> Sheet1, 25, <Link>, ...<more values>
> Sheet2, 13, <Link>, ...<more values>
This is not clear to me, but you can do that by keeping track of the
contents yourself and putting them into a data set.
> - One overall toc sheet linking to the several toc sheets for the
> subsets
You can do that. It's probably best to do it manually - I've never
been happy with the automatic TOCs.
> - Every "normal" sheet (not the toc sheets) must contain a header
> above the data values which has to span across multiple lines and must
> contain a hyperlink to the toc sheet for its subset
This one might be difficult.
> - Some columns in the "normal" sheets must be hidden
I don't remember whether it works, but you could try setting the
column width to 0.
> - several color, formatting and highlighting requirements...
You can't do everything you could possibly think of, but you can do
quite a bit.
> Now how would you guys solve this? Any ideas will greatly be
> appreciated!
You might want to look at this:
http://www.sascommunity.org/wiki/Creating_Summary_and_Detail_Sections_in_an_Excel_Worksheet_Using_the_ExcelXP_Tagset
It doesn't directly answer your questions, but you might get some ideas.
