topic Re: How To Import Multiple Excel Files Into SAS As Separate Tables in SAS Programming

How To Import Multiple Excel Files Into SAS As Separate Tables

Cmags — Thu, 16 Mar 2023 19:01:38 GMT

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.

Re: How To Import Multiple Excel Files Into SAS As Separate Tables

ErikLund_Jensen — Thu, 16 Mar 2023 19:12:43 GMT

Hi @Cmags

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

Get a list of the available Excelfiles in the folder: There are plenty of how-to-exampes in this forum.
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.

Re: How To Import Multiple Excel Files Into SAS As Separate Tables

ballardw — Thu, 16 Mar 2023 20:31:57 GMT

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/497719#M132021

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

Re: How To Import Multiple Excel Files Into SAS As Separate Tables

Cmags — Thu, 16 Mar 2023 21:01:15 GMT

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.

Re: How To Import Multiple Excel Files Into SAS As Separate Tables

Patrick — Thu, 16 Mar 2023 21:36:28 GMT

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.