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

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

fbl204653 — Wed, 01 Feb 2017 22:48:07 GMT

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!

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

Reeza — Wed, 01 Feb 2017 22:00:00 GMT

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

data want;

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

run;

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

fbl204653 — Wed, 01 Feb 2017 22:08:07 GMT

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;

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

Reeza — Wed, 01 Feb 2017 22:20:25 GMT

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

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

art297 — Wed, 01 Feb 2017 22:25:16 GMT

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

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

fbl204653 — Wed, 01 Feb 2017 22:30:28 GMT

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

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

Reeza — Wed, 01 Feb 2017 22:41:07 GMT

It works for me with the XLSX engine, I'm on SAS 9.4 M3

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

art297 — Wed, 01 Feb 2017 22:43:55 GMT

I'm not sure about the vtable view, but you could create the macro variables using proc sql accessing dictionary.tables (e.g.:

libname xlfiles xlsx "/folders/myfolders/test.xlsx";
proc sql;
select *
from dictionary.tables
where libname eq 'XLFILES';
;
quit;

The result of the above is shown in the attached jpg

Art, CEO, AnalystFinder.com

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

fbl204653 — Wed, 01 Feb 2017 22:48:27 GMT

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

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

Reeza — Wed, 01 Feb 2017 22:52:49 GMT

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

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

art297 — Wed, 01 Feb 2017 23:08:00 GMT

What are you trying to do with your prxchange calls? You dont include any prxparse calls that define either re or re2.

Art, CEO, AnalystFinder.com

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

Reeza — Wed, 01 Feb 2017 23:23:52 GMT

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

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

fbl204653 — Thu, 02 Feb 2017 02:19:13 GMT

Hi art, I removed the prxparse part because I think it's not revelant. Thanks for your suggestion! I will try dictionary table in sql..

Thanks!

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

fbl204653 — Thu, 02 Feb 2017 02:20:17 GMT

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

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

fbl204653 — Thu, 02 Feb 2017 02:23:59 GMT

Will do.

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

fbl204653 — Thu, 02 Feb 2017 02:55:50 GMT

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

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

Reeza — Thu, 02 Feb 2017 03:04:28 GMT

@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 🙂

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

RW9 — Thu, 02 Feb 2017 09:42:16 GMT

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;

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

fbl204653 — Thu, 02 Feb 2017 14:38:36 GMT

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