BookmarkSubscribeRSS Feed
art297
Opal | Level 21

:

Your code worked for me as follows.  My workbooks were in the directory d:\art\

you would have to correct the line, below, that states: %let path=d:\art\;

to point to the path were your workbooks exist.

%macro impt(filename,i);

   proc import DATAFILE="&filename" OUT=XL&i DBMS=xlsx REPLACE;

      run;

%mend impt;

%let path=d:\art\;

data _null_;

   command = "dir &path.*.xls /b";;

   infile dummy pipe filevar=command end=eof truncover;

   do i = 1 by 1 while(not eof);

      input path $128.;

      path=catt("&path.",path);

      put 'NOTE: ' path=;

      call execute(cats('%nrstr(%impt(',path,',',i,'));'));

      end;

   stop;

run;

data all;

   set XL:;

run;

clintontull
Calcite | Level 5

How do you do this with pc file server since my OS is unix i cannot import on unix without pc file server.  How can i proc import an xls file without pc file server?  Is there a way?

CCW
Obsidian | Level 7 CCW
Obsidian | Level 7

I've seen some answers about import multiple excel files into SAS. Some are too tedious copying all the code in the reply and too specific to the author's own work. Some are too short by just leaving clues and learning materials. However, we can still learn something from those replies.

Here I want to give a more general answer and the logic behind my program and some explanation.

 

1. Get all the file list in the directory through "pipe"

2. Use macro loop to import excel files in the list.

 

/* 1. Get file lists in the directory*/
filename dirlist pipe 'dir "D:\..." /b';

/* 2. Resolve file names to SAS dataset "files" */
data files;
	length fname $20;	
	infile dirlist truncover length= reclen;
		input fname $varying20. reclen;
run;

/* 3. Store the number of files we want to import into macro variable "nfile" */
data _null_;
	set files nobs= nobs;
		call symput('nfile', nobs);
		stop;
run;

/* 4. Import all the files we want into SAS dataset*/
%macro fileinput;
	%local i;
	%do i= 1 %to &nfile;
	/* Store import path and output filename into macro variables*/
		data _null_;
			set files(firstobs= &i obs= &i);
			/* The length of fpath can't be over 201 since windows limitation */
				call symput('fpath', "D:\..." || fname);
				call symput('foutname', scan(fname, 1, '.') );
		run;
	/*	Excel file import*/
		proc import out= work.&foutname
			datafile= "&fpath"
			dbms=excel replace;
			range="sheet1$"; 
			getnames=yes;
			mixed=no;
			scantext=yes;
			usedate=yes;
			scantime=yes;
		run;
	%end;
%mend;
%fileinput;

 

 

Explanation:

  1. Use unnamed pipe to communicate windows and SAS.
  2. We get the SAS dataset containing our file names we want to import.
  3. For the following macro program, we have to get the total number of files we want to import and store it into a macro variable "nfile". Here we use "call symput".
  4. Finally, use macro loop to import excel files into SAS dataset one by one. Each file is imported into a SAS dataset with the name we specified, "foutname". Here we use scan function to substring the excel file name eliminating its filename extension, .xlsx. Again, the file path and filename are stored into macro variables.
  5. Don't forget to use macro debugging tools like
options symbolgen mprint;
%put ...;

Hope you like it!

vince_ott
Calcite | Level 5

Hi,

Thank you for the solution, I tried the code you posted trying to load multiple excel files from a directory where they are located in Windows. 

 

I keep getting an error message :

ERROR: Physical file does not exist, /SHARED/MCL_Team/MCL_1117//abc.xlsx.  (there are two forward slashes here, but SYMBOLGEN Macro variable FPATH resolves shows correct path.  I wonder what might have added one extra slash to the

datafile="&fpath" directory.

 

 

Here is what the log file looks like:

 

25 GOPTIONS ACCESSIBLE;

SYMBOLGEN: Macro variable NFILE resolves to 24

26 %fileinput;

MPRINT(FILEINPUT): data _null_;

SYMBOLGEN: Macro variable I resolves to 1

SYMBOLGEN: Macro variable I resolves to 1

MPRINT(FILEINPUT): set files(firstobs= 1 obs= 1);

MPRINT(FILEINPUT): call symput('fpath', "/SHARED/MCL_Team/MCL_1117/" || fname);

MPRINT(FILEINPUT): call symput('foutname', outname);

MPRINT(FILEINPUT): run;

NOTE: There were 1 observations read from the data set WORK.FILES.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

 

SYMBOLGEN: Macro variable FPATH resolves to /SHARED/MCL_Team/MCL_1117/xyz_November.xlsx   (singles forward slash which is correct)

 

SYMBOLGEN: Macro variable FOUTNAME resolves to xyz_November

 

MPRINT(FILEINPUT): proc import datafile="/SHARED/MCL_Team/MCL_1117/xyz_November.xlsx

" dbms=xlsx out=xyz_November

replace;

MPRINT(FILEINPUT): RXLX;

MPRINT(FILEINPUT): range="Sheet1$A1:H518";

MPRINT(FILEINPUT): sheet='Sheet1';

MPRINT(FILEINPUT): getnames=yes;

MPRINT(FILEINPUT): run;

ERROR: Physical file does not exist, /SHARED/MCL_Team/MCL_1117//xyz_November.xlsx.  (double forward slashes)

 

Any help is greatly appreciated.

CCW
Obsidian | Level 7 CCW
Obsidian | Level 7
Hi vince_ott,

I programmed the import program months ago. I won't have time to solve the
technical problems of my code. However, I should let you know where you may
find a better regular solution which was already shown by SAS.

You may read SAS macro reference:
http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=mcrolref&docsetTarget=n0ct...

They may use a different approach than mine. It seems like more complicated
but I think more reliable!

Hope you can make your life easier!
jr4sas
Calcite | Level 5

@CCW, I am so in love with you right now! I have spent way too much time trying to import multiple files. Nothing worked, until I tried your code. THANK YOU!!!!

Dobrien912
Calcite | Level 5

Hi, I used the code above to import multiple excel files but the problem I'm having is that instead of the variable 'foutname' being assigned to each dataset, I am just getting a dataset called 'Fname' and it is always whatever file was produced on the last iteration of the do loop. Do you know why SAS isn't capturing the file names and overwriting the same file over and over??? 

Dobrien912
Calcite | Level 5
nevermind. I had it in quotes thats why...now I'm getting a different error. I think because the file names have spaces in them
FreidaHOU
Calcite | Level 5

Dear ALL,

Try the simple code as follows:

 

%let n = 2;

data all;
run;

%macro test;
%do i=1 %to &n;
Proc Import Out= result&i
Datafile="E:\IAR_Rept&i..xlsx"
Dbms=Excel Replace;
Getnames=Yes;
Mixed=No;
Scantext=Yes;
Usedate=Yes;
Scantime=Yes;
Run;
data all;
set all result&i;
run;

%end;
%mend;
%test;

neeleshpandya
Calcite | Level 5

we can import multiple by storing a macro permanently

 

%macro maths(cust,dax)

proc import datafile="s:/workshop/prg1/&cust" dbms=csv out=&dax replace;

run;

%mend maths

 

%maths(customer.csv,mydata)

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
  • 24 replies
  • 15400 views
  • 4 likes
  • 11 in conversation