The SAS Output Delivery System and reporting techniques

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

Reply
Occasional Contributor
Posts: 14

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

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?
SAS Super FREQ
Posts: 8,864

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

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 ssSmiley FrustratedtyleID="_contents">

<Row>
<Cell ssSmiley FrustratedtyleID="contentprocname"><Data ss:Type="String"></Data>
</Cell>
<Cell ssSmiley FrustratedtyleID="contentprocname" ss:HRef="allstuff3.xls#'Table 1 - Data Set SASHELP.CLAS'!A1">
<Data ss:Type="String">Print</Data>
</Cell>
</Row>
<Row>
<Cell ssSmiley FrustratedtyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ssSmiley FrustratedtyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ssSmiley FrustratedtyleID="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 ssSmiley FrustratedtyleID="contentprocname"><Data ss:Type="String"></Data>
</Cell>
<Cell ssSmiley FrustratedtyleID="contentprocname" ss:HRef="allstuff3.xls#'Table 2 - Summary statistics'!A1">
<Data ss:Type="String">Means</Data>
</Cell>
</Row>
<Row>
<Cell ssSmiley FrustratedtyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ssSmiley FrustratedtyleID="contentitem"><Data ss:Type="String"></Data>
</Cell>
<Cell ssSmiley FrustratedtyleID="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]
Occasional Contributor
Posts: 14

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

Posted in reply to Cynthia_sas
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.
Super Contributor
Super Contributor
Posts: 3,174

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

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.
Occasional Contributor
Posts: 14

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

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_
Ask a Question
Discussion stats
  • 4 replies
  • 196 views
  • 0 likes
  • 3 in conversation