DATA Step, Macro, Functions and more

importing all excel files ina folder

Reply
Contributor brm
Contributor
Posts: 30

importing all excel files ina folder

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
Super Contributor
Posts: 317

Re: importing all excel files ina folder

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
Super User
Posts: 9,671

Re: importing all excel files ina folder

[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
Contributor brm
Contributor
Posts: 30

Re: importing all excel files ina folder

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: importing all excel files ina folder

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.
Respected Advisor
Posts: 3,887

Re: importing all excel files ina folder

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
Super User
Posts: 9,671

Re: importing all excel files ina folder

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
Super User
Posts: 9,671

Re: importing all excel files ina folder

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
New Contributor
Posts: 2

Re: importing all excel files ina folder

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

Trusted Advisor
Posts: 1,610

Re: importing all excel files ina folder

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

Regular Contributor
Posts: 196

Re: importing all excel files ina folder

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

New Contributor
Posts: 2

Re: importing all excel files ina folder

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. 

Ask a Question
Discussion stats
  • 11 replies
  • 12751 views
  • 2 likes
  • 9 in conversation