SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

importing many excel data files

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

importing many excel data files

Hi,

suppose that I have 20 excel files file1 ... file20.

To import an individual file, say file 1, I would do the following:

PROC IMPORT OUT= file1 

            DATAFILE= " K:\FilesFormatted\file1.xlsx"

            DBMS= EXCEL REPLACE;

    sheet = "Sheet1";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

(Please note that I want the imported sas data to have the same name as the original excel file).

Is it possible to write a code, perhaps a macro, that will import all the files file1-file20 instead of repeating the above code for each individual excel file?

Thank you


Accepted Solutions
Solution
‎05-03-2015 06:18 AM
Super User
Posts: 9,691

Re: importing many excel data files

As Reeza pointed out . Your CALL EXECUTE statement is not right .

data data_torun;

input filenum;

datalines;

1

2

3

;;;;

run;

data _null_;

   set data_torun;

  call execute(cats('%import_myfile(i=',filenum,')'));

run;

Xia Keshan

View solution in original post


All Replies
Valued Guide
Posts: 3,208

Re: importing many excel data files

Gues what, you forgot you are on a guessing  approach.  (always something to expect to go wrong). No other approach acceptable with reliable data definitions?

---->-- ja karman --<-----
Super User
Posts: 9,691

Re: importing many excel data files

Make a table to contain these file names then CALL EXECUTE()  to execute a macro of proc import .

Super Contributor
Posts: 413

Re: importing many excel data files

Hi Xia,

I searched on the internet (including the sas site) and this is what I could put together:

%macro import_myfile(i=);

proc import file="C:\Documents and Settings\HP_Administrator\Desktop\file\file&i" out=file&i.

DBMS= EXCEL REPLACE;

   sheet = "Sheet1";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

run;

%mend import_myfile;

data data_torun;

input filenum;

datalines;

1

2

3

;;;;

run;

data _null_;

   set data_torun;

   call execute(%import_myfile(filenum));

run;


But unfortunately I got an error message...


Could you please help me to figure out what is wrong with the above code?


Thank you

Super User
Posts: 17,958

Re: importing many excel data files

What's your error?

Your call execute is wrong as well, it expects a string not macro code, something like below, untested

call execute (catt('%import_myfile(', filename, ');'));

Super Contributor
Posts: 413

Re: importing many excel data files

Hi,

it says that more positional parameters found than defined and that the execute doesn't have enough arguments.

Also, I redid  the call execute in string form :

data _null_;

   set data_torun;

   call execute('%import_myfile(filenum)');

run;


but stll the same problem

Super Contributor
Posts: 413

Re: importing many excel data files

I think that I actually managed to do it:

%macro import_myfile(howmany );

/*1. import the files*/

   %do i=1 %to &howmany;

proc import file="C:\Documents and Settings\HP_Administrator\Desktop\file\file&i" out=file&i.

DBMS= EXCEL REPLACE;

   sheet = "Sheet1";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

run;

%end;

/*2.put them all into on table*/

data file;

set file1-file&howmany;

run;

/*3.delete the individual files*/

%do n = 1 %to &howmany;

proc sql;

drop table file&n;

quit;

%end;

%mend import_myfile;

%import_myfile(howmany=3);



The only thing is that for each individual file name (say table1 ... table20 instead of the present file1...file20) I need to change the macro itself. I tried to do %macro import_myfile(howmany, name) but got lots of errors when inside the actual macro changes the file to &name. Could you please help me?


Thank you

Solution
‎05-03-2015 06:18 AM
Super User
Posts: 9,691

Re: importing many excel data files

As Reeza pointed out . Your CALL EXECUTE statement is not right .

data data_torun;

input filenum;

datalines;

1

2

3

;;;;

run;

data _null_;

   set data_torun;

  call execute(cats('%import_myfile(i=',filenum,')'));

run;

Xia Keshan

Super User
Super User
Posts: 7,430

Re: importing many excel data files

Save your data to a proper data file format, for instance CSV.  Write a datastep which imports those CSV, you can use wildcards (*), plenty of topics on here about it:

Trusted Advisor
Posts: 1,631

Re: importing many excel data files

The %FOR macro does this, it doesn't require that you know the names of the Excel files in advance. See the final example at:

http://www.sascommunity.org/wiki/Streamlining_Data-Driven_SAS_With_The_%25FOR_Macro

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 785 views
  • 3 likes
  • 6 in conversation