Hi SAS experts,
I have an excel file containing a couple of sheets, ont of which has " ' " in the sheet name. Then I can't find its name in sashep.vtable after I ran the following code. My guess is " ' " is the cause of the problem. Is there any way to fix it?
libname xls excel 'path';
Thank you so much!
@fbl204653 wrote:
ODBC seems complicated. How to set it up in my case?
Your situation seems complicated if you don't know the structure of your files and they have no naming conventions.
Then again, all data analysis is..
See page 2 and the OLEDB example here.
http://www2.sas.com/proceedings/sugi27/p025-27.pdf
I don't specifically know how to connect to it via ODBC only that its possible. Fortunately I've never had to 🙂
Try using the Literal name reference even if it doesn't show up.
data want;
set xls."this is'my name"n;
run;
Thanks for your reply, Reeza! What I tried to do is, read those sheets one by one based on the macro variabes that have been creatd using sashelp.vtale and vcolumn. Please see below. Since I can't find the sheet name with " ' " in vtable, the sheet can't be impprted .
libname xls excel 'Lab ranges.xlsx';
data sheet;
set sashelp.vtable(where=(libname='XLS'));
run;
data sheetname;
set sheet end=eof nobs=nobs;
st=prxchange(re,-1,memname);
stnm=prxchange(re2,-1,memname);
call symput('site'||left(_n_),strip(memname));
if eof then call symput('lst',strip(_n_));
call symput('sitenum'||left(_n_),strip(st));
call symput('sitename'||left(_n_),strip(stnm));
call symput('siteid'||left(_n_),'_'||compress(stnm,,'kad')||strip(st));
run;
%macro readin;
%do i=1 %to &lst.;
data _null_;
set sashelp.vcolumn(keep=libname memname name type where=(memname="&&site&i.")) end=eof;
if _n_=1 then call execute ("proc sql;create table &&siteid&i. as select");
if type='char' then call execute (name||' length=32 format=$32. informat=$32.');
else call execute ('put('||strip(name)||', best32. -l) as '||strip(name));
if not eof then call execute (',');
else call execute (" from xls.&&site&i..n;quit;");
run;
%end;
%mend readin;
%readin;
Have you tried with an ODBC or XLSX connection instead of EXCEL?
Tried, not working. I guess it's due sas 9.3 Im using.
Yeah, 9.3 doesn't have all the features of XLSX but you can try the ODBC route.
They don't work in 9.3 here. Thank you anyway
ODBC seems complicated. How to set it up in my case?
@fbl204653 wrote:
ODBC seems complicated. How to set it up in my case?
Your situation seems complicated if you don't know the structure of your files and they have no naming conventions.
Then again, all data analysis is..
See page 2 and the OLEDB example here.
http://www2.sas.com/proceedings/sugi27/p025-27.pdf
I don't specifically know how to connect to it via ODBC only that its possible. Fortunately I've never had to 🙂
Also, look at the NLITERAL and QUOTE functions when doing this type of automation. It can help avoid headaches and code that's cumbersome.
Will do.
Firstly, why do you have no idea about your data, this is very bad - worse situation you can be in. Especially using Excel, what happens if your "data" is suddenly pictures in those cells, or a graph is plonked in cell 1. Its like trying to balance jelly on avalanche.
Secondly why all this faffing about with loops macros and code generation? You already seem to know what call execute is about, just use that directly:
libname xls excel 'Lab ranges.xlsx'; data _null_; set sashelp.vcolumn (where=(libname='XLS')); by memname; /* May need to update */ /* Put any calculations here */ if first.memname then call execute('proc sql; create ...'); if type="char" then call execute('...'); else call execute('...'); if last.memname then call execute(';quit;'); run;
Thanks for your first suggestion, RW9! You are right, the loop is not necessary. .
As you can see from the attached, it worked for me. I simply used: libname xlfiles xlsx "/folders/myfolders/test.xlsx";
HTH,
Art, CEO, AnalystFinder.com
Thanks, art297! That part works, since I can't find sheet names in vtable, i can't create macro variables based on that.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.