topic Re: How to import all excel files from a folder also create a new variable to store the file number in SAS Programming

How to import all excel files from a folder also create a new variable to store the file number

gsreddy — Sat, 08 Mar 2025 12:35:49 GMT

Actually i have 1000 excel files in a folder. How to import all excel files from a folder and add all records into data, Also create a new variable in same data set to store the excel file number.

Re: How to import all excel files from a folder also create a new variable to store the file number

PaigeMiller — Sat, 08 Mar 2025 12:44:55 GMT

Your favorite internet search engine finds lots of hits when you search for

SAS read all excel files in a folder

Re: How to import all excel files from a folder also create a new variable to store the file number

ballardw — Sat, 08 Mar 2025 13:50:05 GMT

Warning: The SAS Import engine will make guesses for each file imported as to variable name, type and length. Which with as many files as you have typically means that some of the variables are of different types even from the same column that should be the same type as you perceive the file and the multiple lengths means that combining data sets later may lead to lose of data because of the rules involved when doing such.

IF these files are supposed to be of the same structure it may be worth the effort to save all of the files as CSV (yes 1000 files may take awhile) and write a data step to the resulting files (or import a few of those and edit the generated SAS code) to read such and set the properties you expect for each column. The INFILE statement used with a data step but not in IMPORT will also allow creating a variable using the FILENAME= that you can parse from the file name to add a variable, which I am assuming is meant by "Excel file number".

@gsreddy wrote:

Actually i have 1000 excel files in a folder. How to import all excel files from a folder and add all records into data, Also create a new variable in same data set to store the excel file number.

Re: How to import all excel files from a folder also create a new variable to store the file number

gsreddy — Sat, 08 Mar 2025 18:30:14 GMT

I got info from search engine to import files.looking to store file number in new variable

Re: How to import all excel files from a folder also create a new variable to store the file number

Tom — Sat, 08 Mar 2025 22:31:20 GMT

@gsreddy wrote:
I got info from search engine to import files.looking to store file number in new variable

For detailed help you need to provide more information on what you have already created.

To add a numeric variable to a dataset just run a data step:

data want;
   file_number = 1;
   set have;
run;