BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fbl204653
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

18 REPLIES 18
Reeza
Super User

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

 

data want;

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

run;

 

fbl204653
Obsidian | Level 7

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;

 

 

Reeza
Super User

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

fbl204653
Obsidian | Level 7

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

 

Reeza
Super User

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

fbl204653
Obsidian | Level 7

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

fbl204653
Obsidian | Level 7

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

 

 

 

Reeza
Super User

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

Reeza
Super User

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

fbl204653
Obsidian | Level 7

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

art297
Opal | Level 21

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
fbl204653
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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