topic Re: Macro to import all Excel files in SAS Programming

Macro to import all Excel files

Anneliesant — Fri, 14 Dec 2018 13:05:19 GMT

Hi all,

SAS 9.4

I 'm trying to create a loop so that all the xlsx-files are imported. I notice that the code below is wrong but I don't know how to correct it the right way. I have 4 excelfiles to import. The result of this code is that the first excelfile is imported 4 times (and it overwrites itself each time). The result should be that 4 each excel file is imported once. Each excelfile has a different variable in its filename (ERK = 009, 090,091,777). Only the 009 is imported. Could anyone help me with this please?

/*macro to import a single excel*/
%macro import(input);

OPTIONS VALIDVARNAME=ANY;
PROC IMPORT OUT=work.B1B2_E&erk. (keep = N_BUD_AN ID_AGR TIT_N Source N2018)
DATAFILE= "&path\&input..xlsx"
DBMS=excelcs REPLACE;
RANGE="B1B2$"; /*to import the sheet completely*/
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

%mend;

/*loop to import all the files*/
%macro Loadall;
%do i=1 %to &numobs;
%import(&&filein&i);
%end;

%mend;

%loadall;

Re: Macro to import all Excel files

Kurt_Bremser — Fri, 14 Dec 2018 13:09:45 GMT

Since you do not change &erk in the loop (or derive it somehow from &input), the dataset is always overwritten with the data from the latest import.

Re: Macro to import all Excel files

Anneliesant — Fri, 14 Dec 2018 13:34:53 GMT

How do I write the code?

Before the macro this is written.

/*Each Excel file gets a variable, number*/
data _null_;
set mydata.deelnemerslijst end=endobs;/*end=variable --> at the last observation it starts counting*/
call symputx('fileIN'!!left(_n_),excel_file);/*takes the name and puts a number in front "n" (_n_ counts the lines --> this combination is named 'fileIN'*/
if endobs=1 then call symputx('numobs',_N_);
run;

Re: Macro to import all Excel files

Kurt_Bremser — Fri, 14 Dec 2018 13:39:30 GMT

You need to make up a rule for building the macro variable erk.

Re: Macro to import all Excel files

RW9 — Fri, 14 Dec 2018 14:19:30 GMT

There are many examples of this type on here, please use the search functionality:
https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&q=import+multiple+excel+files