BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GreggB
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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

 

 

 

 

View solution in original post

4 REPLIES 4
collinelliot
Barite | Level 11

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
Super User

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
Super User

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
Super User

@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

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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