topic Re: importing all excel files ina folder in SAS Programming

importing all excel files ina folder

brm — Mon, 13 Jun 2011 23:39:51 GMT

Hi,

I have 6 excel files in one folder ex: c:/data.....I want to import all those files into SAS.

Can somebody help me with the code,how can i do it with programming,instead of importing each file.

one more thing,is there anyway we can import(only one file which is added to that folder later using program).

Please somebody help me.

Thanks,
brm

Re: importing all excel files ina folder

milts — Tue, 14 Jun 2011 00:53:15 GMT

Hi,

You can use the x command to create a text file listing the contents of your directory then importing it to a dataset. Create also a flag that would help you identify that the excel file is not yet loaded.

After this you can create a loop which iterates over that dataset as your input parameter on proc import.

After this flag the read items that it has already been imported.

If I have a free time I'll try to post the actual code here.

Hope this helps. Feel free to comment is it sounds confusing

Regards,
Milton

Re: importing all excel files ina folder

Ksharp — Tue, 14 Jun 2011 05:07:28 GMT

[pre]
%let subdir=D:\sasdata\;
filename dir pipe "dir &subdir.*.xls /B";
data new;
infile dir truncover end=last;
input filename $100.;
filename=cats("&subdir",filename);
call symputx(cats('path',_n_),filename);
call symputx(cats('dsn',_n_),scan(scan(filename,6,'\'),1,'.'));
if last then call symputx('nobs',_n_);
run;
%put _user_;
%macro import;
%do i=1 %to &nobs;
proc import datafile="&&path&i" out=&&dsn&i dbms=excel replace;
getnames=yes;
mixed=yes;
run;
%end;
%mend import;

%import
[/pre]

Ksharp

Re: importing all excel files ina folder

brm — Tue, 14 Jun 2011 05:41:42 GMT

If i run above code...getting these errors.Can you guide me how to resole that filename statement error.

16 %let subdir=C:\Trade-NewSet-06132011\new;
17 filename dir pipe "dir &subdir.*.xls /B";
ERROR: Insufficient authorization to access PIPE.
ERROR: Error in the FILENAME statement.
18 data new;
19 infile dir truncover end=last;
20 input filename $100.;
21 filename=cats("&subdir",filename);
22 call symputx(cats('path',_n_),filename);
23 call symputx(cats('dsn',_n_),scan(scan(filename,6,'\'),1,'.'));
24 if last then call symputx('nobs',_n_);
25 run;

ERROR: No logical assign for filename DIR.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.NEW may be incomplete. When this step was stopped there were 0
observations and 1 variables.

%macro import;
28 %do i=1 %to &nobs;
29 proc import datafile="&&path&i" out=&&dsn&i dbms=excel replace;
30 getnames=yes;
31 mixed=yes;
32 run;
33 %end;
34 %mend import;
WARNING: Apparent symbolic reference NOBS not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: &nobs
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro IMPORT will stop executing.

Thanks,
brm.

Re: importing all excel files ina folder

sbb — Tue, 14 Jun 2011 11:04:26 GMT

Searching the SAS.COM support website, you will need to contact your SAS admin for assistance - here is a Google advanced search argument that revealed a SAS USAGE NOTE:

ERROR: Insufficient authorization to access PIPE site:sas.com

Usage Note 16109: Insufficient Authorization to access pipe error message when connecting to Spawner programs
http://support.sas.com/kb/16/109.html

Scott Barry
SBBWorks, Inc.

Re: importing all excel files ina folder

Patrick — Tue, 14 Jun 2011 11:20:05 GMT

Besides of the noxcmd issue:

If this is a once-off task and nothing repetitive then use EG's import wizard instead of coding.

Reason: The import wizard does also some data cleansing for you like removing control characters in text fields and the like. I had just lately "a lot of fun" doing this programatically.

The wizard allows you also to embed your source data into the code in case you want to re-run things later on - so the import wizard step has only to be done once.

HTH
Patrick Message was edited by: Patrick

Re: importing all excel files ina folder

Ksharp — Fri, 17 Jun 2011 05:16:38 GMT

From your log, it looks like you can not use PIPE function which maybe be deny by Administrator.
Are you using SAS/EG or other SAS client software? If you can ,let Administrator to open it, If you can not ,You can use dopen() fopen() and so on ,these are mentioned by SASKiwi before, search it in forum ,you will find answer.

Ksharp

Re: importing all excel files ina folder

Ksharp — Mon, 20 Jun 2011 04:06:15 GMT

Or you can use filename *.xls to get list of xls filename.
This is an example.Not test fully.
[pre]

%let subdir=c:\temp\;
filename dir "&subdir.*.xls ";
data new;
length filename fname $ 50;
infile dir eof=last filename=fname;
input ;
last: filename=fname;
run;
proc sort data=new nodupkey;
by filename;
run;
data _null_;
set new;
call symputx(cats('filename',_n_),filename);
call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.'), ,'ka');
if last then call symputx('nobs',_n_);
run;
%put _user_;
%macro import;
%do i=1 %to &nobs;
proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;
getnames=yes;
mixed=yes;
run;
%end;
%mend import;

%import
[/pre]

Ksharp

Re: importing all excel files ina folder

ernie2014 — Wed, 21 May 2014 19:30:42 GMT

I changed a few things to Ksharp's answer and it worked for me:

%let subdir=C:\path\;

filename dir "&subdir.*.xlsx ";

data new;

length filename fname $ 200;

infile dir eof=last filename=fname;

input ;

last: filename=fname;

run;

proc sort data=new nodupkey;

by filename;

run;

data null;

set new;

call symputx(cats('filename',_n_),filename);

call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.'), ,'ka'));

call symputx('nobs',_n_);

run;

%put &nobs.;

%macro import;

%do i=1 %to &nobs;

proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;

getnames=yes;

mixed=yes;

run;

%end;

%mend import;

%import

Re: importing all excel files ina folder

PaigeMiller — Wed, 21 May 2014 20:02:16 GMT

The %for Macro will answer the original question (and work on subfolders as well)

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

Re: importing all excel files ina folder

gzr2mz39 — Fri, 05 Sep 2014 17:57:15 GMT

Hi PaigeMiller,

I tried this:

%let topfolderpath=C:\Users\Desktop\Test_Import;

%for(filepath, in=<&topfolderpath>, do=%nrstr(

%let subfolderpath=&filepath;

%for(filepath shortname, in=<&subfolderpath>, do=%nrstr(

proc import out=&shortname

datafile="&filepath"

DBMS=EXCEL REPLACE;

RANGE="ALL_INJ$";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

run;

))

And I got this error. I tested these paths given below and they should work fine.

ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_8-28-14.xlsx

ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_8_26_14.xlsx

ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_8_27_14.xlsx

ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_9-2-14.xlsx

ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_9-3-14.xlsx

ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_9-4-14.xlsx

Re: importing all excel files ina folder

myng — Sat, 15 Oct 2016 04:03:43 GMT

Hi, I tried exactly the same syntax that you posted and got error messages highlighting the "=" in the "dbms=excel" Would appreciate help with what I need to change. Thank you!

NOTE 137-205: Line generated by the invoked macro "IMPORT".
35 proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;
-
22
35 ! getnames=yes; mixed=yes;
35 ! run;
ERROR 22-322: Syntax error, expecting one of the following: DATAFILE, DATATABLE, DBMS, FILE, OUT,
TABLE.

NOTE: Line generated by the invoked macro "IMPORT".
35 proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;
-
76
35 ! getnames=yes; mixed=yes;
35 ! run;
ERROR 76-322: Syntax error, statement will be ignored.

Re: importing all excel files ina folder

anup_sas — Wed, 29 Nov 2017 14:29:31 GMT

thanks for sharing this Sir

Re: importing all excel files ina folder

Vibhaa — Tue, 19 Jun 2018 17:06:43 GMT

Can you please explain (eof and filename) in the below code ?

%let subdir=c:\temp\;
filename dir "&subdir.*.xls ";
data new;
length filename fname $ 50;
infile dir eof=last filename=fname;
input ;
last: filename=fname; 
run;

Thanks!