DATA Step, Macro, Functions and more

SAS 9.4 and xls files

Accepted Solution Solved
Reply
Super Contributor
Posts: 279
Accepted Solution

SAS 9.4 and xls files

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.


Accepted Solutions
Solution
‎03-30-2017 07:58 AM
Super User
Posts: 19,770

Re: SAS 9.4 and xls files

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

 

@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

 

 

 

 

View solution in original post


All Replies
PROC Star
Posts: 307

Re: SAS 9.4 and xls files

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.

Super User
Posts: 19,770

Re: SAS 9.4 and xls files

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...

Super User
Posts: 11,343

Re: SAS 9.4 and xls files

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.

 

 

Solution
‎03-30-2017 07:58 AM
Super User
Posts: 19,770

Re: SAS 9.4 and xls files

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

 

@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

 

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 171 views
  • 2 likes
  • 4 in conversation