BookmarkSubscribeRSS Feed
brm
Calcite | Level 5 brm
Calcite | Level 5
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
13 REPLIES 13
milts
Pyrite | Level 9
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
Ksharp
Super User
[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
brm
Calcite | Level 5 brm
Calcite | Level 5
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Patrick
Opal | Level 21
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
Ksharp
Super User
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
Ksharp
Super User
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
ernie2014
Calcite | Level 5

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

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
gzr2mz39
Quartz | Level 8

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

myng
Calcite | Level 5

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. 

anup_sas
Calcite | Level 5

thanks for sharing this SirSmiley Happy

Vibhaa
Fluorite | Level 6

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 20956 views
  • 3 likes
  • 11 in conversation