BookmarkSubscribeRSS Feed
Spf2010
Calcite | Level 5
I am new to sas. I am trying to figure out how to import every excel file in a folder without naming each file.(apox 100) Also there is one sheet I am looking to grab from every file and combine them into one data set. I have done a lot of research but nothing has worked.
11 REPLIES 11
Reeza
Super User

Here's one approach. 

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

 

However, in my experience this won't work overall because when you use PROC IMPORT it guesses at types and the types will not be match across all files unless you happen to have really clean files. 

If you can convert them to CSV and customize the import procedure that's most likely to be the best way. In fact if it's all text files you can read them all at once into a single file relatively easily. 

 


@Spf2010 wrote:
I am new to sas. I am trying to figure out how to import every excel file in a folder without naming each file.(apox 100) Also there is one sheet I am looking to grab from every file and combine them into one data set. I have done a lot of research but nothing has worked.

 

SuryaKiran
Meteorite | Level 14

You can use x-commands to read the files in a folder and then import. If you don't have access to run x-commands then the following approach might work for you.

FILENAME _folder_ "%bquote(/usr/folder/)";/* Your path here */
data filenames(keep=memname);
FORMAT memname $100.;
  handle=dopen( '_folder_' );
  if handle > 0 then do;
    count=dnum(handle);
    do i=1 to count;
      memname=dread(handle,i);
      output filenames;
    end;
  end;
  rc=dclose(handle);
run;
filename _folder_ clear;

The above program reads all the files that are in the folder. No you can use the logic to filter your files (ie. by extensions or file names or etc.). Put those names in a macro using PROC SQL INTO .

/* Count total files in the location and add all the file names into a macro */
PROC SQL noprint;
SELECT COUNT(*),memname INTO: Total_Files, : All_Files separated by "," 
FROM filenames;
QUIT;

%PUT "Total Files:" &Total_Files "All Files:" &All_Files;

Using a macro to loop through each file and import them.

 

%MACRO IMPORT_FILES();
%DO I=1 %TO &Total_Files ;
%LET File=%SCAN("&All_Files",&i,",");
proc import 
  datafile="/usr/folder/&File."  /* Your folder path here */
  dbms=xlsx 
  out=GRIDWORK.TASK&i. 
  replace;
sheet="sheet1"; /* Your desired sheet here */
RUN;
%END;
%MEND IMPORT_FILES;
%IMPORT_FILES();

 Finally Append all the datasets.

/* Append imported data into a single dataset */ 
%MACRO APPEND();
%DO I=2 %TO &Total_Files;
PROC APPEND BASE=WORK.Task1 DATA=WORK.Task&i. FORCE;
RUN;
%END;
%MEND APPEND;
%APPEND();
Thanks,
Suryakiran
Spf2010
Calcite | Level 5
The results from this is just one column name memname.Do you know why this would be?
SuryaKiran
Meteorite | Level 14

Please post the code your trying and the log.

Thanks,
Suryakiran
Spf2010
Calcite | Level 5
figured out the issue. However there are a lot of spaces and headings for each file when it appends. How do I clean that up?
SuryaKiran
Meteorite | Level 14

You haven't provided much information. What files are you trying to import (xls, xlsx, csv,etc.)? Does your all files have first row with variables names and data starts from row 2? What code are you using?

Thanks,
Suryakiran
Spf2010
Calcite | Level 5
I tried the Range="sheet$A4:0" it didn't work.
Spf2010
Calcite | Level 5
Xlsx.The sheet has headers rows 1 to 4. Rows 5 and 6 the columns are merged for 2 or 3 columns and 2 or 3 are not.I can't post my code because of work rules. I am using Surya's code.
Reeza
Super User

Make fake data and code and test it. Once it's working, you apply it to your real data. Often when following this process you'll usually solve the issues before posting.

 


@Spf2010 wrote:
Xlsx.The sheet has headers rows 1 to 4. Rows 5 and 6 the columns are merged for 2 or 3 columns and 2 or 3 are not.I can't post my code because of work rules. I am using Surya's code.

 

 

SuryaKiran
Meteorite | Level 14

RANGE is only for xls files and will not work if your files are xlsx. I suggest you use GETNAMES=NO with DATAROW=n (n- row where data starts). Once the file is imported then rename the column names.

 

Alternate approach, convert the files to CSV or txt and then use data step to read the files.

Thanks,
Suryakiran
Spf2010
Calcite | Level 5
It get rid of the headers but I still have blank rowe between each dataset that appends

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 1203 views
  • 0 likes
  • 3 in conversation