Text mining and content categorization

Importing multiple excel sheets with a macro loop

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Importing multiple excel sheets with a macro loop

Hey there,

So I've been spinning my wheels at this for way too long that I feel like there must be a better way.

I have an excel workbook that I have assigned a libname and I would like to do formatting on the sheets which are labeled in a way that I thought would be conducive to set up a loop. Unfortunately the fact that the sheets are named with single quotes around them makes referencing a macro variable difficult.  This is my closest attempt but the quotes get lost in the call symputx statement.

Any help would be greatly appreciated

libname &facil_export. "&path.\&facil_export._clean.xlsx";

%macro import_loop;

%do i = 1 %to &periods.;

%let sheet = "Period (&i.)$";

%let the_n = n;

data _null_;

format sheet_v2 $15.;

sheet_v2 = tranwrd(&sheet.,'"',"'");

call symputx('sheet_final', sheet_v2);

run;

data &facil_export._&i.; set &facil_export..&sheet_final.&the_n.;

run;

%end;

%mend;

%import_loop;


Accepted Solutions
Solution
‎01-28-2014 09:26 PM
New Contributor
Posts: 3

Re: Importing multiple excel sheets with a macro loop

Thanks guys - I ended up going a different route and utilizing the sashelp.vstabvw table to create an index of the sheet names.  This paper outlines the technique - I appreciate the feedback though !

http://www2.sas.com/proceedings/sugi31/034-31.pdf

View solution in original post


All Replies
Super User
Super User
Posts: 6,122

Re: Importing multiple excel sheets with a macro loop

I do not understand the problem.  What SAS code are you trying to generate?

Note that name literals (and other SAS literals) can use double quotes or single quotes.


%let i=5 ;

%put "Period (&i)$"n;

Note that you can use single quotes around macro variables if they are not the outer quotes.

%let i=5 ;

%put "'Period (&i)$'"n;


New Contributor
Posts: 3

Re: Importing multiple excel sheets with a macro loop

Hey Tom,

The issue I'm having is that, using the libname engine - a excel sheet needs to have the single quotes around it to be referenced and since i'm trying to reference many sheets i'm using a macro loop.  I haven't been able to figure out how I can output the sheet name (ie - 'Period (1)'n Period (2)'n etc.) with the macro do-loop variable.

I tried both of your examples above but with no luck.

Super User
Super User
Posts: 6,122

Re: Importing multiple excel sheets with a macro loop

First get it to work typing them by hand. 

If the code you posted generates the string you want, but it does not work in might be a macro quoting issue.  Try either generating the concatenated text to a new macro variable or wrapping it in a %unquote() function.

set %unquote(&facil_export..&sheet_final.&the_n.) ;

Super User
Super User
Posts: 6,122

Re: Importing multiple excel sheets with a macro loop

Are you sure of your sheet names?

8     options validvarname=any;

9

10    %let i=1;

11    data x&i;

12     set xx."'Part (&i)$'"n ;

13     run;

NOTE: There were 31 observations read from the data set XX."'Part (1)$'"n.

NOTE: The data set WORK.X1 has 31 observations and 4 variables.

Respected Advisor
Posts: 2,993

Re: Importing multiple excel sheets with a macro loop

Have a look at my example that uses dictionary tables and PROC DATASETS rather than macro looping. It does rely on the Excel tabs conforming to SAS dataset naming conventions though:

https://communities.sas.com/message/170166#170166

Solution
‎01-28-2014 09:26 PM
New Contributor
Posts: 3

Re: Importing multiple excel sheets with a macro loop

Thanks guys - I ended up going a different route and utilizing the sashelp.vstabvw table to create an index of the sheet names.  This paper outlines the technique - I appreciate the feedback though !

http://www2.sas.com/proceedings/sugi31/034-31.pdf

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 1822 views
  • 6 likes
  • 3 in conversation