DATA Step, Macro, Functions and more

Importing sheets from excel *MACRO*

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Importing sheets from excel *MACRO*

[ Edited ]

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


Accepted Solutions
Solution
‎10-16-2017 04:26 AM
Super User
Posts: 10,534

Re: Importing sheets from excel *MACRO*

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

Like Joe's Garage.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎10-16-2017 04:26 AM
Super User
Posts: 10,534

Re: Importing sheets from excel *MACRO*

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

Like Joe's Garage.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

Re: Importing sheets from excel *MACRO*

Posted in reply to KurtBremser

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

 

Thank you very much!!

Super User
Super User
Posts: 9,813

Re: Importing sheets from excel *MACRO*

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 228 views
  • 1 like
  • 3 in conversation