topic Re: Import excel with no SAS/ACESS interface to PC files in SAS Procedures

Import excel with no SAS/ACESS interface to PC files

helloSAS — Wed, 20 Nov 2013 16:27:18 GMT

Hi,

I'm trying to use proc import as below and I'm getting an error. I'm guess its because i do not have SAS/ACCESS interface to PC files.

PROC IMPORT
DATAFILE='/uhome/snoone/callrecording.xls'
DBMS=EXCEL
OUT=x
REPLACE; run;

ERROR: DBMS type EXCEL not valid for import.

What is an alternative way of importing excel file?

Re: Import excel with no SAS/ACESS interface to PC files

snoopy369 — Wed, 20 Nov 2013 18:06:15 GMT

I don't recall if DBMS=XLS requires access to PC Files, you could try that. Otherwise you're probably out of luck for XLS. XLSX can be read in as a text file, given some work (it's a zipped XML file), but odds are it's cheaper to license Access to PC Files.

Re: Import excel with no SAS/ACESS interface to PC files

ballardw — Wed, 20 Nov 2013 18:21:58 GMT

If you don't have access to PC files you may be best off with saving the file to CSV. Then you can either write Proc Import for that file or use the wizards.

One big, at least in my opinion, of using the CSV approach is that Proc Import generates basic datastep code, look in the log or use F4 key in the editor to recall. The code can be modified to control variable types, informats and formats including using custom informats/formats. I also usually add labels to the code.

Re: Import excel with no SAS/ACESS interface to PC files

Reeza — Wed, 20 Nov 2013 18:35:52 GMT

This was posted to SAS L, perhaps you can follow up with them?

SAS-L archives -- November 2013, week 3 (#107)</title><style type="text/css"></style><head><title>LISTSERV at the University of Georgia

Re: Import excel with no SAS/ACESS interface to PC files

snoopy369 — Wed, 20 Nov 2013 18:38:07 GMT

If you're referring to Gady's post, that very likely only would work on XLSX (I wrote roughly the same thing a year ago, and FriedEgg wrote a simpler routine as well). XLSX is an xml file, XLS may be a binary file (it also may be a very differently structured XML file).