topic Re: Importing sheets from excel MACRO in SAS Programming

Importing sheets from excel MACRO

jbs23 — Mon, 16 Oct 2017 07:08:53 GMT

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 &NUM;



%macro MYLOOP;
     %do i=1 %to &NUM;
     %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 &NUM;
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

Re: Importing sheets from excel MACRO

Kurt_Bremser — Mon, 16 Oct 2017 08:12:43 GMT

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

Like Joe's Garage.

Re: Importing sheets from excel MACRO

jbs23 — Mon, 16 Oct 2017 08:27:43 GMT

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

Thank you very much!!

Re: Importing sheets from excel MACRO

RW9 — Mon, 16 Oct 2017 09:01:00 GMT

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.

topic Re: Importing sheets from excel *MACRO* in SAS Programming

Importing sheets from excel *MACRO*

Re: Importing sheets from excel *MACRO*

Re: Importing sheets from excel *MACRO*

Re: Importing sheets from excel *MACRO*

topic Re: Importing sheets from excel MACRO in SAS Programming

Importing sheets from excel MACRO

Re: Importing sheets from excel MACRO

Re: Importing sheets from excel MACRO

Re: Importing sheets from excel MACRO