topic Re: Generating a TOC in Excel - formulas are being written as text in ODS and Base Reporting

Generating a TOC in Excel - formulas are being written as text

_Altons_ — Thu, 19 Feb 2009 16:11:04 GMT

Hi,

I am writing out a SAS data set which contains a TOC to excel but all formulas are being interpretated as text due to a single quote at the beginning of the function.

My code is below:

%LET xlsinfo="C:\emp.xls";
LIBNAME xls &xlsinfo VER=2002;

Proc SQL;
Create table mysheets as
Select distinct tranwrd(scan(memname,1,'$'),"'","") as Sheetid
from sashelp.vstabvw where libname="XLS" and upcase(tranwrd(scan(memname,1,'$'),"'","")) ne "TOC";
quit;
proc datasets library=Xls nodetails; delete TOC 'TOC$'n; quit;

Data xls.TOC ;
set mysheets;
link="=HYPERLINK("!!'"#"'!!'&CELL("address",'!!"'"!!strip(compbl(Sheetid))!!"'!A1),"!!'"'!!strip(Sheetid)!!'")';
drop sheetid;
run;

LIBNAME xls clear;

Any ideas?

Re: Generating a TOC in Excel - formulas are being written as text

Cynthia_sas — Thu, 19 Feb 2009 17:07:20 GMT

Hi:
Not sure what's up with your code. However, as an alternative, this code, using TAGSETS.EXCELXP does create a TOC as the first page of a multi-sheet workbook. I used the version of the ExcelXP tagset template dated (SAS 9.1.3, v1.75, 07/26/07).

How Excel wants the TOC link specified looks like this in the Spreadsheet Markup Language XML written by ExcelXP. Note that in the XML Markup, the contents link is set by the ss:HRef= attribute.

But, I don't know how the LIBNAME engine for Excel generates an internal hyperlink. This might actually be a question for Tech Support.

cynthia

Example of XML SpreadsheetML contents written by TAGSETS.ExcelXP:
[pre]
<Worksheet ss:Name="Contents">
<Table ss:StyleID="_contents">

<Row>
<Cell ss:StyleID="contentprocname"><Data ss:Type="String"></Data>
</Cell>
<Cell ss:StyleID="contentprocname" ss:HRef="allstuff3.xls#'Table 1 - Data Set SASHELP.CLAS'!A1">
<Data ss:Type="String">Print</Data>
</Cell>
</Row>
<Row>
<Cell ss:StyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ss:StyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ss:StyleID="contentitem" ss:HRef="allstuff3.xls#'Table 1 - Data Set SASHELP.CLAS'!A1">
<Data ss:Type="String">Data Set SASHELP.CLASS</Data>
</Cell>
</Row>
<Row>
<Cell ss:StyleID="contentprocname"><Data ss:Type="String"></Data>
</Cell>
<Cell ss:StyleID="contentprocname" ss:HRef="allstuff3.xls#'Table 2 - Summary statistics'!A1">
<Data ss:Type="String">Means</Data>
</Cell>
</Row>
<Row>
<Cell ss:StyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ss:StyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ss:StyleID="contentitem" ss:HRef="allstuff3.xls#'Table 2 - Summary statistics'!A1">
<Data ss:Type="String">Summary statistics</Data>
</Cell></Row>
</Table>
</Worksheet>
[/pre]

Code that generated the above XML (I do get a TOC in the file when Excel opens):
[pre]
ods tagsets.excelxp file='allstuff2.xls'
options(doc='Help' contents='yes')
style=sasweb;

proc print data=sashelp.class;
run;

proc means data=sashelp.shoes;
class region;
var sales;
run;

ods tagsets.excelxp close;
[/pre]

Re: Generating a TOC in Excel - formulas are being written as text

_Altons_ — Thu, 19 Feb 2009 17:26:15 GMT

Thanks! however I think I was not enough clear with this situation...

I've got an excel file with a bunch of sheets, and every month i'll be adding/deleting some sheets. What my code does is:

1) read all those sheets through a EXCEL LIBNAME and put them into a SAS table (that the SQL bit).
2) Clear the content of the TOC sheet (if exist)
3) Write back to excel all worksheet's name formatted as links/hiperlinks. Each link will point out one sheet.
4) Clear the libname to release the file.

Everything works fine a part from the following issue:

Rather than getting this in every cell:
=HYPERLINK("#"&CELL("address",'Dictionary Tables'!A1),"Dictionary Tables")

I am getting this:
'=HYPERLINK("#"&CELL("address",'Dictionary Tables'!A1),"Dictionary Tables")

which is being read as text when actually is a formula.

Re: Generating a TOC in Excel - formulas are being written as text

sbb — Thu, 19 Feb 2009 21:04:40 GMT

At first glance of the SAS code you sent in your initial post, I can see that your LINK variable assignment starts with a double-quote, however it does not end with a same double-quote (I see a single-quote in your code submitted).

Scott Barry
SBBWorks, Inc.

Re: Generating a TOC in Excel - formulas are being written as text

_Altons_ — Fri, 20 Feb 2009 10:25:28 GMT

Well, I just found that the Excel engine does not have the capacity to write formulas into a cell...

I may go then with either dde or vbs... :-s

Thanks for your help.

_Altons_