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?
... View more