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

Posted in reply to philfoot123

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: 7,860

Re: Importing multiple excel sheets with a macro loop

Posted in reply to philfoot123

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: 7,860

Re: Importing multiple excel sheets with a macro loop

Posted in reply to philfoot123

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: 7,860

Re: Importing multiple excel sheets with a macro loop

Posted in reply to philfoot123

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.

Super User
Posts: 3,777

Re: Importing multiple excel sheets with a macro loop

Posted in reply to philfoot123

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

Posted in reply to philfoot123

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 and locked.

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

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