Hi,
i ran this code for other excel and works perfectly. The problem is im trying to do the same with other excel and have this problem on the log.
libname MYLIB pcfiles type=excel path="I:\blabla\blabla\blablabla\blablabla.xlsx";
proc sql;
create table TABLE1
as select *
from dictionary.tables
where libname="MYLIB";
quit;
proc sql;
select MEMNAME
into :NAMELIST separated by '*'
from TABLE1;
quit;
proc sql;
select count(MEMNAME)
into :NUM
from TABLE1;
quit;
%put &NAMELIST;
%put #
%macro MYLOOP;
%do i=1 %to #
%let NAME1=%scan(&NAMELIST,&i,*);
%let NAME2=%substr(&NAME1,1,%length(&NAME1)-1);
data &NAME2;
set MYLIB."&NAME1"n;
if missing(cats(of _all_)) then delete;
run;
%end;
%mend MYLOOP;
%MYLOOP
43 %macro MYLOOP;
44 %do i=1 %to #
45 %let NAME1=%scan(&NAMELIST,&i,*);
46 %let NAME2=%substr(&NAME1,1,%length(&NAME1)-1);
47
48 data &NAME2;
49 set MYLIB."&NAME1"n;
50 if missing(cats(of _all_)) then delete;
51 run;
52 %end;
53
54 %mend MYLOOP;
55
56 %MYLOOP
ERROR: Literal contains unmatched quote.
ERROR: The macro MYLOOP will stop executing.
Why can this happen? how can i fix it?
Thank you very much in advance
Know your data. Since Excel allows all kinds of crap as sheet names, you probably have a quote in there.
Like Joe's Garage.
Know your data. Since Excel allows all kinds of crap as sheet names, you probably have a quote in there.
Like Joe's Garage.
Yep, the problem was two sheets named with a space between two words!. ("Table 25" and "Table 26")
Thank you very much!!
Why can you not just use proc copy to copy all the existing data from the libname to a new name? SAS will automatically create valid SAS names for each of the datasets. Alternatively you could use a data _null_ step, and call execute the code for each tab.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.