How To Import Multiple Excel Files Into SAS As Separate Tables

[Cmags]

I am looking for guidance as to how I can import all Excel Files in a folder on my laptop into SAS...see below:

Can I write some code to import all 3 of these excel files into SAS as their own table? I want to do it without having to go one by one. Thanks.

[ErikLund_Jensen]

Hi @Cmags 

You can do that. The general receipe is the following:

1. Get a list of the available Excelfiles in the folder: There are plenty of how-to-exampes in this forum.
2. Write a small macro to loop over the file list and

• allocate the file as an XLSK-libname
• Use proc copy to copy the excel-sheet to a table in your output SAS libname.

[ballardw]

Just one of the replies to this question: https://communities.sas.com/t5/SAS-Programming/Issue-with-importing-all-excel-files-in-folders/m-p/4...

The accepted solution is basically one want to implement what @ErikLund_Jensen describes.

[Cmags]

I am very new to SAS, & am struggling to identify what needs to be changed in that code in order to make it work for what I need. Does most of it stay as is? Thanks.

[Patrick]

If you're very new to SAS and this is a one off task then you'll be quickest to use the Studio or EG import Wizard and do it one by one. Won't take very long.

If you want to learn how this can be done via code then look-up Proc Import in the SAS docu. 

With Proc Import make things work for a single Excel and then just copy paste the code just changing the path the Excel you want to import.

Don't use code samples as your starting point that got %macro code in it. If you're very new to SAS then it's just too early/too much to get into this.

One of the things that's important: Where is your SAS running. NOT the client (Studio/EG) but the server to which you connect to as that's where the SAS code executes and from where the Excels must be accessible. 

...If you use the Studio/EG import wizard then the Excels can be local to your client and Studio/Excel will do the upload to the server side for you.