topic Importing multiple excel sheets with a macro loop in SAS Data Science

Importing multiple excel sheets with a macro loop

philfoot123 — Tue, 28 Jan 2014 00:43:30 GMT

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;

Re: Importing multiple excel sheets with a macro loop

Tom — Tue, 28 Jan 2014 02:12:07 GMT

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;

Re: Importing multiple excel sheets with a macro loop

philfoot123 — Tue, 28 Jan 2014 18:19:42 GMT

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.

Re: Importing multiple excel sheets with a macro loop

Tom — Tue, 28 Jan 2014 21:40:30 GMT

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.) ;

Re: Importing multiple excel sheets with a macro loop

SASKiwi — Tue, 28 Jan 2014 22:48:03 GMT

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

Re: Importing multiple excel sheets with a macro loop

Tom — Tue, 28 Jan 2014 22:59:42 GMT

Are you sure of your sheet names?

8 options validvarname=any;

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.

Re: Importing multiple excel sheets with a macro loop

philfoot123 — Wed, 29 Jan 2014 02:26:38 GMT

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