topic Re: PROC IMPORT and xls file in SAS Programming

PROC IMPORT and xls file

GreggB — Tue, 14 Jan 2020 17:33:43 GMT

This code worked fine until recently:

proc import datafile="G:fakepath\&year.\Preliminary\3701_Prelim_Score_Roster - 14JAN19.xls"
out=eoc dbms=excelcs replace;
run;

Now, I get this error:

ERROR: Failed to connect to the Server: .
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.10 seconds
      cpu time            0.03 seconds

I wondered if my latest SAS renewal was the issue. Here's what I'm running on a Windows 64bit desktop:

Product(s) Ordered		Expiration Date
SAS Analytics Pro, including the products:
• Base SAS		November 29, 2020
• SAS/GRAPH		November 29, 2020
• SAS/STAT		November 29, 2020
SAS Enterprise Guide		November 29, 2020
SAS/ACCESS Interface to ODBC		October 30, 2019
SAS/ACCESS Interface to PC Files		November 29, 2020

Re: PROC IMPORT and xls file

JeanDo — Tue, 14 Jan 2020 17:46:28 GMT

Hello,

Is your running-code environment weel-connected to your local windows? Are you working with SAS Studio or SAS EG?

Regards,

Re: PROC IMPORT and xls file

Reeza — Tue, 14 Jan 2020 18:16:21 GMT

Is your PC Files Server running? I would recommend trying dbms=excel for an excel file. Otherwise, the next step would be to see if the PC FILE Server is installed, launch it and then try the code again.

Re: PROC IMPORT and xls file

GreggB — Tue, 14 Jan 2020 19:01:33 GMT

I changed my code to dbms=excel and got a different error.

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.20 seconds
cpu time 0.07 seconds

Re: PROC IMPORT and xls file

GreggB — Tue, 14 Jan 2020 19:02:37 GMT

I'm running Base SAS 9.4

Re: PROC IMPORT and xls file

Tom — Tue, 14 Jan 2020 19:13:30 GMT

Why not try using DBMS=XLS and avoid trying to depend on getting SAS to talk to Windows/Microsoft code?

Re: PROC IMPORT and xls file

GreggB — Tue, 14 Jan 2020 19:37:48 GMT

When I do that it reads column 1 only (in a weird fashion) like so:

should have:

3701551

instead:

3 7 0 1

Re: PROC IMPORT and xls file

Tom — Tue, 14 Jan 2020 20:50:34 GMT

What is producing the XLS file? Are they really old? If not then perhaps your process can produce the more modern (and better documented) XLSX format instead?

Re: PROC IMPORT and xls file

GreggB — Tue, 14 Jan 2020 21:23:08 GMT

the xls file comes from an outside vendor. not sure why they won't switch over to xlsx format

Re: PROC IMPORT and xls file

JeanDo — Tue, 14 Jan 2020 21:34:20 GMT

Hi,
Dis toi try this method :

libname test pcfiles path=your-path-to-the-file;

data toto;
set test.sheet1;
run;

When sheet1 is the name of the sheet you want to ingest

Re: PROC IMPORT and xls file

Reeza — Tue, 14 Jan 2020 23:25:25 GMT

If ExcelCS doesn't work, PCFILES won't work.
@GreggB, did you try turning on your PCFILE server and seeing if it works? It is an application you have to start if you're not working on a SAS server at an organization where it's managed by someone else.

Re: PROC IMPORT and xls file

Reeza — Tue, 14 Jan 2020 23:26:31 GMT

Worse case scenario, this VBS code will convert an XLSX to CSV, and you could modify it to convert an XLS to CSV or XLSX and see if either of those formats worked.

https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e