- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try using the Literal name reference even if it doesn't show up.
data want;
set xls."this is'my name"n;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Have you tried with an ODBC or XLSX connection instead of EXCEL?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Tried, not working. I guess it's due sas 9.3 Im using.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yeah, 9.3 doesn't have all the features of XLSX but you can try the ODBC route.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
They don't work in 9.3 here. Thank you anyway
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
ODBC seems complicated. How to set it up in my case?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Also, look at the NLITERAL and QUOTE functions when doing this type of automation. It can help avoid headaches and code that's cumbersome.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Will do.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your first suggestion, RW9! You are right, the loop is not necessary. .
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, art297! That part works, since I can't find sheet names in vtable, i can't create macro variables based on that.