SAS 9.4 and xls files

[GreggB]

I've been given many xls files (not my choice) to read into SAS. How can I do this without opening each file and saving it as a csv or xlsx file?  I'm running 9.4 on a Windows 64 bit machine.

[collinelliot]

You should not have any issues just because it's xls. What products do you have for SAS/ACCESS? Try running "proc setinit; run;" to see what you have licensed.

[Reeza]

You can use the solution linked below, with modifications for xls files. 

In the first macro, change the extension to xls.

In the second change the DBMS=xls to Excel or xls.

https://communities.sas.com/t5/Base-SAS-Programming/importing-files-from-different-subdirectories-an...

[ballardw]

Be advised that if the files are supposed to have the same structure that any approach relying on Proc Import will likely have variables that should be the same name and type may vary in results from file to file. Type, numeric or character, length of character variables varying are very common.

It may be worth while to run down a VB script to do file save to CSV and read with a data step so any supposedly alike files are indeed similar when brought into SAS.

[Reeza · Accepted solution]

@ballardw awww, now he won't post a next question since you've warned him 🙂

@GreggB Proc Import is a guessing procedure, but it doesn't guess accurately all the time. So if you import all the Excel files, it's 99% guaranteed that certain variables will be numeric in one file and character in another. This becomes an issue when you try and combine the datasets which is usually the next logical step in this process. So you'll end up either processing every file to make sure it meets the specifications at some point or converting all to CSV and reading accordingly.

The SAS provided VB script here can be modified to convert all your xls to csv files automatically.

http://support.sas.com/kb/43/496.html