IMPORT MULTIPLE EXCEL FILES

[andreas_lds]

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

[HamidK]

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!

[HamidK]

Oleg,

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

Regards,
Hamid.

[Oleg_L]

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]

[PaigeMiller]

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