topic Re: importing many excel data files in SAS Data Management

importing many excel data files

ilikesas — Thu, 30 Apr 2015 19:57:49 GMT

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

Re: importing many excel data files

jakarman — Thu, 30 Apr 2015 20:32:20 GMT

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?

Re: importing many excel data files

Ksharp — Fri, 01 May 2015 05:58:50 GMT

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

Re: importing many excel data files

RW9 — Fri, 01 May 2015 08:04:12 GMT

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:

Re: importing many excel data files

PaigeMiller — Fri, 01 May 2015 13:42:10 GMT

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

Re: importing many excel data files

ilikesas — Sat, 02 May 2015 21:54:24 GMT

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;

;;;;

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

Re: importing many excel data files

Reeza — Sat, 02 May 2015 22:04:41 GMT

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, ');'));

Re: importing many excel data files

ilikesas — Sat, 02 May 2015 22:07:44 GMT

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

Re: importing many excel data files

ilikesas — Sat, 02 May 2015 22:47:24 GMT

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

Re: importing many excel data files

Ksharp — Sun, 03 May 2015 10:18:49 GMT

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

data data_torun;

input filenum;

datalines;

;;;;

run;

data _null_;

set data_torun;

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

run;

Xia Keshan