IMPORT MULTIPLE EXCEL FILES

[sasbegginer]

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

[LinusH]

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

[sasbegginer]

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

[LinusH]

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

[Peter_C]

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

[Oleg_L]

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]

[Dingdang]

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

[Dingdang]

HI Oleg,

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

BR  Dingdang

[Dingdang]

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

[Dingdang]

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

[art297]

: 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.

[Oleg_L]

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

[HamidK]

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;

[SASKiwi]

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.

[HamidK]

SASkiwi,

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

Thanks.