topic Re: Import one sheet from multiple excel files in SAS Data Management

Import one sheet from multiple excel files

Spf2010 — Tue, 14 Aug 2018 20:13:55 GMT

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.

Re: Import one sheet from multiple excel files

Reeza — Tue, 14 Aug 2018 20:26:19 GMT

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.

Re: Import one sheet from multiple excel files

SuryaKiran — Tue, 14 Aug 2018 20:32:33 GMT

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();

Re: Import one sheet from multiple excel files

Spf2010 — Wed, 15 Aug 2018 13:02:16 GMT

The results from this is just one column name memname.Do you know why this would be?

Re: Import one sheet from multiple excel files

SuryaKiran — Wed, 15 Aug 2018 13:21:31 GMT

Please post the code your trying and the log.

Re: Import one sheet from multiple excel files

Spf2010 — Wed, 15 Aug 2018 14:36:04 GMT

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?

Re: Import one sheet from multiple excel files

Spf2010 — Wed, 15 Aug 2018 14:57:11 GMT

I tried the Range="sheet$A4:0" it didn't work.

Re: Import one sheet from multiple excel files

SuryaKiran — Wed, 15 Aug 2018 14:57:26 GMT

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?

Re: Import one sheet from multiple excel files

Spf2010 — Wed, 15 Aug 2018 15:05:02 GMT

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.

Re: Import one sheet from multiple excel files

Reeza — Wed, 15 Aug 2018 15:10:15 GMT

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.

Re: Import one sheet from multiple excel files

SuryaKiran — Wed, 15 Aug 2018 15:13:04 GMT

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.

Re: Import one sheet from multiple excel files

Spf2010 — Wed, 15 Aug 2018 15:26:42 GMT

It get rid of the headers but I still have blank rowe between each dataset that appends