Importing multiple Excel files into one SAS file

[AK100]

Hi all,

I have 5 Excel files, each of the file contains 5 sheets, each sheet contains around 50 rows. So this means 1 file has in total 50 x 5 = 250 rows. Is there a speedy trick I can use to combine all these 5 files (including sheets) into 1 SAS file with in total 250 x 5 rows = 12.500 rows?

Please note I'm using SAS EG 7.15. Appreciate your help all.

[Kurt_Bremser]

To concatenate all sheets in one swoop, you will have to assign libnames to all Excel files and then derive the sheet names from sashelp.vtable.

How you can automate the libnames depends on the path and file names of the Excel files.

[AK100]

Hi Kurt, that was a long time.

I don't really understand what you mean...all the files are in 1 folder on my desktop.

[Kurt_Bremser]

There are two ways to read Excel files into SAS:

• PROC IMPORT - reads one sheet at a time
• LIBNAME XLSX - makes all sheets in a workbook available as pseudo-datasets in a single library

Since all your files reside in one directory, the process would be (it is required that the SAS session has physical access to the directory, so this won't work with a remote server)

• retrieve a listing of the directory (DOPEN, DREAD functions in a data step)
• use this dataset of filenames to dynamically create a series of libnames with a fixed prefix
• retrieve all MEMNAMEs from SASHELP.VTABLE, for entries where LIBNAME starts with the prefix, into a macro variable
• Use this in the SET statement of the data step that creates your wanted dataset

If your SAS session runs on a remote server, the Excel files must first be made available to it (copy to the server, or use a network resource shared on your desktop and the SAS server)

For more detailed help, you need to supply additional information regarding your SAS architecture and possible directory and file names.

[AK100]

So the 2nd option applies for me, which sounds quiet complex... but okay thank you for your answer.

[Kurt_Bremser]

If you would not use the Excel file format, but textual data instead (like CSV files), it would require just a single data step:

data want;
length fname infile_name $200;
retain infile_name;
infile "/path/*.csv" dlm=',' dsd filename=fname;
if fname ne infile_name
then do;
  input; /* skips header line of csv */
  infile_name = fname;
end;
input
  /* add your variable inputs here */
;
run;

Note that textual data is always better to work with than Excel files. ALWAYS.

[andreas_lds]

In theory combining the sheets is as easy as @Kurt_Bremser explained it, but with real-world excel-files, things get interesting in 7 of 9 cases, because excel is hardly usable as file-format for data - this has been discussed so many time, i won't repeat it again. So, after you have assigned all libraries, you have to verify that each variable has the same type and length in all datasets/sheets.

[AlanC]

Let me weigh in here on a couple of things. How you approach is up to you but there are a few things to point out. Kurt has mentioned 2 ways to read Excel but there are other ways as well. Excel is ODBC and OleDb capable so you can use the Access engines too. I don't know your environment so not sure what you have. Your Excel format is also just a zipped XML file so the XML engine can also be used. Additionally, you can read Excel using any of the libraries in other languages and push it to SAS (EPPlus, GemBox, Aspose, etc.) or, better yet, do all of the combination there and change the data storage means. There is also the dreaded VBA.

Also, look at using AbleBits to do this since it provides that capability plus loads more features.

IMO, CSV or textual storage is a very poor choice. Why? It lacks a type and the type has to be inferred in some way. Excel is bad since it lacks typing but SAS also has limited typing (chars/nums). If you change storage means, JSON, XML, DBMS all have better typing but the latter is more preferred.

If you have SAS Access to ODBC, consider using it. If you have Microsoft Access, consider importing the data there and then using ODBC.

Lacking that, consider VBA in Excel or one of the SAS snippets here.

[AK100]

Thank you all, sounds pretty complex all.

[AlanC]

Not at all. Try ODBC if you have it. Simple libname.

[Kurt_Bremser]

---

@AlanC wrote:

Not at all. Try ODBC if you have it. Simple libname.

---

But before that, you have to create a DSN for each Excel file, IIRC.

[AlanC]

Kurt,

I had no issue with the approaches you suggested merely that there were more than 2 ways to handle. I can easily envision a dozen but the complexity, to a SAS user, varies. As far as DSN is concerned, use the noprompt option and specify it in a string with a macro loop if desired. That eliminates the DSN requirement. I use noprompt primarily with SQL Server so let me know of any issues with Excel.

My other issue, in the thread, was with text files as a good choice. Yes, agree on ease of use but is this a number or string: 'ABC123'? Without more info, you can't be sure. That is a problem.  

[Kurt_Bremser]

You won't use text files for working storage, but for data transfer needs and long-time preservation they are the best option, by lightyears.

A text file is a text file is a text file, and has been so for five decades (introduction of ASCII), with little to no disruptions (the most recent one being UTF). I can inspect text files with the most simple tools (vi on UNIX), and because of no in-built proprietary type or structure information, they give control to the receiving software system and its users. I actually consider this lack of forced typing one of the most important positive features of text files.

[Reeza]

Here's one way. But what can often happen in these cases especially with PROC IMPORT is that the data is imported with different variable types or formats which makes it harder to combine them after the fact. You'll have to include a step where you check if each variable has the right type/format to combine them as well, especially if this needs to be an automatic process.

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