Importing sheets from excel *MACRO*

[jbs23]

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

[Kurt_Bremser · Accepted solution]

Know your data. Since Excel allows all kinds of crap as sheet names, you probably have a quote in there.

Like Joe's Garage.

[jbs23]

Yep, the problem was two sheets named with a space between two words!. ("Table 25" and "Table 26")

Thank you very much!!

[RW9]

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.