topic Re: IMPORT MULTIPLE EXCEL FILES in SAS Programming

IMPORT MULTIPLE EXCEL FILES

sasbegginer — Tue, 12 Oct 2010 10:51:05 GMT

Hi,

I have about 10 excel files with the same column heading and i need a way to import all off them in one table (appending one after another). I can write a proc import statement for each one followed by a proc append statement but how would i do it all together? Thanks

Re: IMPORT MULTIPLE EXCEL FILES

LinusH — Tue, 12 Oct 2010 11:25:47 GMT

Well, as you described:

PROC IMPORT ....

PROC APPEND

PROC IMPORT ....

PROC APPEND
etc.

If you want to save some programming line you could wrap it into a macro.

/Linus

Re: IMPORT MULTIPLE EXCEL FILES

sasbegginer — Tue, 12 Oct 2010 11:35:00 GMT

Yes but any help writing a macro which doesnt require the file name and imports all the files in a folder...

Re: IMPORT MULTIPLE EXCEL FILES

LinusH — Tue, 12 Oct 2010 11:58:08 GMT

See this as strictly for inspiration, totally untested. See on-.line doc for references.

%macro importAll(DIR = );

%let rc = %sysfunc(filename(dir,&DIR));
%let did = %sysfunc(dopen(dir));
%let nfiles = %sysfunc(dnum(did));

%do I = 1 to &NFILES;

%let ExcelFile = %sysfunv(dread(did,&I));

proc import file="&DIR\&ExcelFile." out=ExcelData. ...;
...
run;

proc append base=AllData data=ExcelData;
run;

%end;

%let rc = %sysfunc(dclose(did));
%let rc = %sysfunc(filename(dir));

%mend importAll;

%importAll(dir=C:\data\MyExcelFiles);

/Linus

Re: IMPORT MULTIPLE EXCEL FILES

Oleg_L — Tue, 12 Oct 2010 12:41:30 GMT

You can try this example too.

[pre]

%macro w2sas05(input=d:\dep\input\plast\2009\w4_2,out=work.plast);

/* read files in directory */
%let dir=%str(%'dir %")&input.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&dir);
data list1; length fname $256.;
infile myfiles truncover;
input myfiles $100.;
/* put _infile_;*/
fname=quote(upcase(cats("&input",'\',myfiles)));
drop myfiles;
run;
filename myfiles clear;

%let j=1;
%let dsid=%sysfunc(open(list1));
%let rc=%sysfunc(fetch(&dsid));
%do %while(&rc=0);
%let file=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,fname))));

PROC IMPORT DBMS=EXCEL2002 OUT= _&j
DATAFILE= &file REPLACE ;
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
DBSASLABEL=NONE;
TEXTSIZE=100;
RUN;

proc append data=_&j base=&out; run;

proc delete data=_&j; run;

%let rc=%sysfunc(fetch(&dsid));
%let j=%eval(&j+1);
%end;
%let rc=%sysfunc(close(&dsid));

%mend w2sas05;

[/pre] Message was edited by: Oleg_L

Re: IMPORT MULTIPLE EXCEL FILES

Peter_C — Wed, 13 Oct 2010 08:08:40 GMT

Any way of using data step infile option FILENAME= and *.xls as the file to collect the file names without reading through the whole file?
Then the file names become available for a call execute invocation of PROC import for each workbook.
just an interesting thought. ....

Re: IMPORT MULTIPLE EXCEL FILES

HamidK — Thu, 17 Feb 2011 18:07:24 GMT

Oleg,

Great macro.

It works fine in native SAS 9.2 but I get the following error messages when running it on Enterprise Guide 4.3:

ERROR: Insufficient authorization to access PIPE.
ERROR: Error in the FILENAME statement.

Any idea anyone what I can do to make it work in Enterprise Guide? Here is the modified macro code I am using:

%macro w2sas05(input=c:\Data\SAStests\ScrapTies\, out=work.DailyTemp);
* read files in directory ;
%let dir=%str(%'dir %")&input.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&dir);
data list1;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;
* put _infile_;
fname=quote(upcase(cats("&input",'\',myfiles)));
drop myfiles;
run;
filename myfiles clear;
%let j=1;
%let dsid=%sysfunc(open(list1));
%let rc=%sysfunc(fetch(&dsid));
%do %while(&rc=0);
%let file=%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,fname))));

PROC IMPORT DBMS=EXCEL OUT= _&j
DATAFILE= &file REPLACE ;
SHEET="summary";
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=NO;
SCANTIME=NO;
DBSASLABEL=NONE;
TEXTSIZE=100;
RUN;
proc append force data=_&j base=&out;
run;
proc delete data=_&j;
run;
data &out;
set &out;
run;
%let rc=%sysfunc(fetch(&dsid));
%let j=%eval(&j+1);
%end;
%let rc=%sysfunc(close(&dsid));
%mend w2sas05;

%w2sas05;

Re: IMPORT MULTIPLE EXCEL FILES

SASKiwi — Thu, 17 Feb 2011 20:52:30 GMT

Your macro executes an operating system DIR command to get a list of files. By default this ability to execute an operating system command is turned off in EG. A change to your SAS server options would be required, for example I suspect XCMD = OFF.

Re: IMPORT MULTIPLE EXCEL FILES

HamidK — Thu, 17 Feb 2011 21:03:37 GMT

SASkiwi,

How can I change the server options in EG? I did not find anything under >Tools>Options.

Thanks.

Re: IMPORT MULTIPLE EXCEL FILES

andreas_lds — Fri, 18 Feb 2011 08:06:52 GMT

This option is defined in the server-metadata and be altered by your sas-administrators only.

Re: IMPORT MULTIPLE EXCEL FILES

Oleg_L — Fri, 18 Feb 2011 12:01:24 GMT

Greetings!

May be this version of macro will work on your default EG settings.
I can not test it in EG. I don't have it.

[pre]

%macro cfin(cfindir=d:\data\2011\sks\,out=work.plast);

%let filrf=mydir;
%let rc=%sysfunc(filename(filrf,"&cfindir"));
%let did=%sysfunc(dopen(&filrf));
%let lstname=;
%let memcount=%sysfunc(dnum(&did));
%if &memcount > 0 %then %do;
%do i=1 %to &memcount;
%let lstname=%sysfunc(dread(&did,&i));
%let file=&cfindir.&lstname;
PROC IMPORT DBMS=EXCEL2002 OUT= _&i
DATAFILE= "&file" REPLACE ;
RANGE='PRV_RATE$A1:F65536';
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
DBSASLABEL=NONE;
TEXTSIZE=100;
RUN;
proc append data=_&i base=&out; run;
proc delete data=_&i; run;
%end;
%let rc=%sysfunc(dclose(&did));
%end;
%mend cfin;

options mprint=1;
%cfin(cfindir=d:\data\2011\sks\,out=work.plast);

[/pre]

Re: IMPORT MULTIPLE EXCEL FILES

HamidK — Fri, 18 Feb 2011 14:09:38 GMT

Andreas,

Thank you for your response. However, I am have SAS and EG installed on my standalone computer. I found references to changing the settings re. XCMD in another thread:

http://support.sas.com/forums/thread.jspa?messageID=28370

I will figure out how to change my PC settings - I don't have admin rights to it!

Re: IMPORT MULTIPLE EXCEL FILES

HamidK — Fri, 18 Feb 2011 14:10:54 GMT

Oleg,

Thank you. This works. I will use it for now until I get the XCMD enabled on my SAS configuration.

Regards,
Hamid.

Re: IMPORT MULTIPLE EXCEL FILES

Oleg_L — Mon, 21 Feb 2011 13:45:39 GMT

Peter,
your interesting thought is working.
Thank you for the idea.

[pre]

%macro sks2sas01(input=d:\work\test1,out=work.tt);
/* read files in directory */
%let dir=%str(%'dir %")&input.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&dir);
data list1; length fname $256.;
infile myfiles truncover;
input myfiles $100.;
/* put _infile_;*/
fname=quote(upcase(cats("&input",'\',myfiles)));
out="&out";
drop myfiles;
call execute('
PROC IMPORT DBMS=EXCEL2002 OUT= _1
DATAFILE= '||fname||' REPLACE ;
RANGE="PRV_RATE$A1:F65536";
GETNAMES=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
DBSASLABEL=NONE;
TEXTSIZE=100;
RUN;
proc append data=_1 base='||out||' force; run;
proc delete data=_1; run;
');
run;
filename myfiles clear;

%mend sks2sas01;

%sks2sas01(input=d:\work\test1,out=work.tt);

[/pre]

Re: IMPORT MULTIPLE EXCEL FILES

Dingdang — Thu, 26 Sep 2013 13:59:18 GMT

hi Oleg,

your code looks great but í've got a small problem running it (since i am an absolute beginner in sas, i dont really fully understand your code as to say where my problem is). i have a file with 10 test data tables and there is only one table in each of them, containing the variables a b c d...n and each variable has 30 random values. I suppose RANGE gives the area where the data should be read, so i adjusted my code to 'RANGE="table1$A1:N30"' otherweise i didnt change any part of the code. and then i ran it and got the following warning message:

WARNING: Variable a was not found on BASE file. The variable will not be added to the BASE file.

and this for every variable a to n. As a result contains work.tt no columns.

why did this happen and how should i adjust my code so it works?

Many thanks again!

BR Dingdang

Re: IMPORT MULTIPLE EXCEL FILES

Dingdang — Thu, 26 Sep 2013 15:02:27 GMT

HI Oleg,

problem solved as i changed RANGE to SHEET. but many thanks anyway

BR Dingdang

Re: IMPORT MULTIPLE EXCEL FILES

PaigeMiller — Thu, 26 Sep 2013 15:29:35 GMT

The %For macro has examples of importing multiple Excel spreadsheets, see the last example at

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

Re: IMPORT MULTIPLE EXCEL FILES

Dingdang — Thu, 24 Oct 2013 14:09:40 GMT

Hi Oleg,

i used your code on another file. the excelsheets that i want to read into SAS have about 5000 variables and each of them have only 1 Observation. You code worked great but only 255 Variables were read into SAS, and each of them had then 8 observations (only the first one was originally in my excelsheets and the other 7 are just empty ones). why did this happen? how can i correct it so that all the variables could be correclty read into SAS?

thanks again for your super help!!

BR Dingdang

Re: IMPORT MULTIPLE EXCEL FILES

Dingdang — Thu, 24 Oct 2013 14:21:30 GMT

ok. found the problem. SAS can only import and export 255 variable when you access excel 2007 files. is it possible that i modify the code, so that SAS read 255 variables in once at a time and then merge them all together in the end?

Many thanks. BR Dingdang

Re: IMPORT MULTIPLE EXCEL FILES

art297 — Thu, 24 Oct 2013 14:39:30 GMT

: You really ought to create a new discussion rather than just ask a new question as a reply to an old discussion. In that new discussion you really ought to describe your problem better, specifically to show the code you are using. SAS can read all columns from an Excel 2007 workbook.