DATA Step, Macro, Functions and more

Can't find sheet with a " ' " in name in Sashelp.vtable

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Can't find sheet with a " ' " in name in Sashelp.vtable

[ Edited ]

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
Solution
‎02-02-2017 10:16 AM
Super User
Posts: 19,837

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

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 Smiley Happy

View solution in original post


All Replies
Super User
Posts: 19,837

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

Try using the Literal name reference even if it doesn't show up.

 

data want;

set xls."this is'my name"n;

run;

 

Contributor
Posts: 36

Re: Can't find sheet with a " ' " in name in Sashelp.table

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;

 

 

Super User
Posts: 19,837

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

Have you tried with an ODBC or XLSX connection instead of EXCEL?

Contributor
Posts: 36

Re: Can't find sheet with a " ' " in name in Sashelp.table

Tried, not working. I guess it's due sas 9.3 Im using.

 

Super User
Posts: 19,837

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

Yeah, 9.3 doesn't have all the features of XLSX but you can try the ODBC route.

Contributor
Posts: 36

Re: Can't find sheet with a " ' " in name in Sashelp.table

They don't work in 9.3 here. Thank you anyway

Contributor
Posts: 36

Re: Can't find sheet with a " ' " in name in Sashelp.table

ODBC seems complicated. How to set it up in my case?

 

 

 

Solution
‎02-02-2017 10:16 AM
Super User
Posts: 19,837

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

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 Smiley Happy

Super User
Posts: 19,837

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

Also, look at the NLITERAL and QUOTE functions when doing this type of automation. It can help avoid headaches and code that's cumbersome.

Contributor
Posts: 36

Re: Can't find sheet with a " ' " in name in Sashelp.table

Will do.

Super User
Super User
Posts: 7,977

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

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;

 

Contributor
Posts: 36

Re: Can't find sheet with a " ' " in name in Sashelp.table

Thanks for your first suggestion, RW9! You are right, the loop is not necessary. Man Tongue .   

PROC Star
Posts: 7,486

Re: Can't find sheet with a " ' " in name in Sashelp.table

Posted in reply to fbl204653

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

 

 

 


Capture.JPG
Contributor
Posts: 36

Re: Can't find sheet with a " ' " in name in Sashelp.table

Thanks, art297! That part works, since I can't find sheet names in vtable, i can't create macro variables based on that. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 216 views
  • 0 likes
  • 4 in conversation