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

[gsreddy]

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. 

[PaigeMiller]

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

SAS read all excel files in a folder

[gsreddy]

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

[Tom]

---

@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;

[ballardw]

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. 

---