BookmarkSubscribeRSS Feed
Calcite | Level 5

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";
proc datasets library=Xls nodetails; delete TOC 'TOC$'n; quit;

Data xls.TOC ;
set mysheets;
drop sheetid;

LIBNAME xls clear;

Any ideas?
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.


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

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

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

proc print data=sashelp.class;

proc means;
class region;
var sales;

ods tagsets.excelxp close;
Calcite | Level 5
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.
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Calcite | Level 5
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.



Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3 in conversation