ODS tagsets.excelxp: What is possible and what not?
in [SAS]
|
From: Alex on 1 Oct 2009 11:36 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 - 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> - One overall toc sheet linking to the several toc sheets for the subsets - 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 - Some columns in the "normal" sheets must be hidden - several color, formatting and highlighting requirements... Now how would you guys solve this? Any ideas will greatly be appreciated! Thanks a lot! Alex
From: Stephen Dybas on 1 Oct 2009 13:09 Alexander, Sounds like a tall order. I would start by running a Google search on: Creating Multi-Sheet Excel Workbooks the Easy Way with SAS ( http://support.sas.com/rnd/papers/sgf07/sgf2007-excel.pdf ) or You Use SAS®, Your Boss Uses Excel. Guess Where Your Results Are Going to Appear! Using ODS to Create Your Results in Excel Steve Dybas On Thu, 1 Oct 2009 08:36:03 -0700, Alex <alexander.konn(a)IEA-DPC.DE> 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 >- 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> >- One overall toc sheet linking to the several toc sheets for the >subsets >- 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 >- Some columns in the "normal" sheets must be hidden >- several color, formatting and highlighting requirements... > >Now how would you guys solve this? Any ideas will greatly be >appreciated! > >Thanks a lot! >Alex
From: Jack Hamilton on 1 Oct 2009 22:16 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(a)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.
From: Alex on 2 Oct 2009 10:28 On Oct 2, 4:16 am, j...(a)STANFORDALUMNI.ORG (Jack Hamilton) wrote: > 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 > j...(a)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... > > It doesn't directly answer your questions, but you might get some ideas. Thanks to Steven, Jack and John for very helpful suggestions! I managed to achieve practically everything I wanted. Best, Alex
|
Pages: 1 Prev: Need Help with Excel headers Next: PROC IMPORT Bug in 9.2 TS2M0 |