topic Re: importing excel into SAS in SAS Programming

importing excel into SAS

Navin2015 — Wed, 14 Jan 2015 19:13:18 GMT

I do have a base SAS 9.4 installed on my computer . looks like there are some restrictions to it. I have been trying to import the excel files into the SAS but it is not letting me do it. I tried using older excel format 2003-007 and also tried using latest excel 2013. none of them work.

here is the log.

NOTE: Additional host information:

X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

NOTE: SAS initialization used:

real time 1.33 seconds

cpu time 0.56 seconds

3 libname xdb excel "C:\Users\nabinneupane\Desktop\IP.xlsx";

ERROR: The EXCEL engine cannot be found.

ERROR: Error in the LIBNAME statement.

5 PROC IMPORT OUT= WORK.EMPLOYEE

6 DATAFILE= "C:\Users\nabinneupane\Desktop\IP.xlsx"

7 DBMS=xlsx REPLACE ;

ERROR: DBMS type XLSX not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process tiing eceme):

real time 0.00 seconds

cpu time 0.00 seconds

8 GETNAMES=YES;

9 MIXED=NO;

10 SCANTEXT=YES;

11 USEDATE=YES;

12 SCANTIME=YES;

13 RUN;

Re: importing excel into SAS

Reeza — Wed, 14 Jan 2015 20:35:38 GMT

Do you have SAS/Access licensed and installed?

Try running the following to see what's installed and included in your version of SAS.

proc setinit; run;

and/or

proc productstatus; run;

Re: importing excel into SAS

Navin2015 — Wed, 14 Jan 2015 21:20:46 GMT

I did and this is what it was on the log file.

Grace Period: 45 days (ending 29OCT2015).

Warning Period: 45 days (ending 13DEC2015).

System birthday: 27NOV2014.

Operating System: WX64_WKS.

Product expiration dates:

---Base SAS Software

14SEP2015

---SAS/STAT

14SEP2015

---SAS/GRAPH

14SEP2015

---SAS/Secure 168-bit

14SEP2015

---SAS/Secure Windows

14SEP2015

---SAS Enterprise Guide

14SEP2015

---SAS Workspace Server for Local Access

14SEP2015

---High Performance Suite

14SEP2015

but I was able to export from SAS to Excel not just been able to import excel files.

Re: importing excel into SAS

Jagadishkatam — Thu, 15 Jan 2015 05:29:04 GMT

Hi Reeza,

I was earlier aware of proc setinit, but product status is something i came to know from you. Thanks for that.

i tried to run proc productstatus;run; however just a small correction:smileysilly:, it is product_status.

Thanks,

Jag

Re: importing excel into SAS

Ksharp — Thu, 15 Jan 2015 05:39:45 GMT

DBMS=excelcs ?

DBMS=xls ?

Re: importing excel into SAS

Kurt_Bremser — Thu, 15 Jan 2015 07:57:45 GMT

Save the Excel spreadsheet as .csv and import that.

- you don't need any additional SAS license (saves $$$)

- importing in a data step gives you more control over the data, a structural change will generate an error

- the data step is completely platform-independent, you only have to look at the charsets when you transfer data between platforms

Re: importing excel into SAS

overmar — Sun, 18 Jan 2015 03:34:50 GMT

Navin,

I had the same problem with 9.3, but it resolved with 9.4, but only when the SAS/Microsoft Office Plugin was properly installed. At the moment the issue that you likely are currently having though is you need to use dbms=excel.

Hope this helps

Re: importing excel into SAS

jakarman — Sun, 18 Jan 2015 17:46:54 GMT

I do not see the access to pcfiles product.

Without that one many of the interfaces to excel will not be there.

There are some work arround but the Libname excel engine and proc import will not operate on native excel