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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.