topic Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4 in SAS Procedures

proc import 64-bit excel 2010 file into 64-bit SAS 9.4

KHaavik — Tue, 24 Jun 2014 19:15:58 GMT

I am trying to read an excel file into SAS. Our office has had problems doing this in the past, and typically saves a spreadsheet as csv and imports that. Boss wants a direct read from excel. As far as I know this should be possible because we have 64-bit SAS, 64-bit Office, and SAS access to PC file formats. I have tried using both .xlxs and .xlsb extensions. The file exists, is readable, and is not in use by anyone else. So why do I keep getting this error? I would appreciate any insights.

proc import datafile="H:\var selection model for macro\TQIP Forced Variables by Cohort.xlsb"

39 ! out=tmp replace;

40 run;

ERROR: Unable to open file H:\var selection model for macro\TQIP Forced Variables by Cohort.xlsb.
It does not exist or it is already opened exclusively by another user, or you need
permission to view its data.

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

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.07 seconds

cpu time 0.06 seconds

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

ballardw — Tue, 24 Jun 2014 21:33:15 GMT

I am guessing that since you don't specify the dbms=Excel or other source option SAS is attempting to import a text file and failing.

Also, I have on occasion had files "locked" by Excel until I opened and closed another workbook.

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

KHaavik — Tue, 24 Jun 2014 21:57:07 GMT

I have also tried it with a dbms= excel and dbms=exelcs. I guess I should have included more of the log. I just opened the spreadsheet, saved it and closed it. Still no joy.

I was hoping I had a coding error but I am beginning to think SAS just won't do it.

11 Proc import datafile="I:\TQIP\2014 Analyses\Fall Report\Modeling\TQIP Forced Variables by

11 ! Cohort.xlsb"

12 dbms=excel replace out=newdata ;

13 Run;

ERROR: Unable to open file I:\TQIP\2014 Analyses\Fall Report\Modeling\TQIP Forced Variables by

Cohort.xlsb. It does not exist or it is already opened exclusively by another user, or you

need permission to view its data.

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

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.09 seconds

cpu time 0.04 seconds

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

KHaavik — Tue, 24 Jun 2014 22:14:53 GMT

I got it. Finally tried using the point & click import wizard and saving the code. It gave me pretty much what I had already tried, but this version works so I'll take it. I had avoided the wizard because we needed a programmatic solution. Silly me.

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

PGStats — Tue, 24 Jun 2014 22:19:05 GMT

So, for the benefit of others (like me ) what was wrong? - PG

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

KHaavik — Tue, 24 Jun 2014 22:44:52 GMT

This is the code which worked.

PROC IMPORT OUT= WORK.forcevars

DATAFILE= "I:\TQIP\2014 Analysis\Fall Report\Modeling\TQIP Forced Variables by Cohort.xlsx"

DBMS=EXCEL REPLACE;

RANGE="'Forced Variables$'";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

As far as I can tell, the thing I had wrong was that I did not have the range argument formed as single quotes followed by a dollar sign inside the double-quotes. I would never have figured it out on my own.

Karina

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

PGStats — Wed, 25 Jun 2014 00:11:27 GMT

Thanks! That might have been a problem, but I think the "does not exist" error message was caused by the spelling of "2014 analyses" in the path name. - PG

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

KHaavik — Wed, 25 Jun 2014 00:23:06 GMT

Oh for Pete's sake. How embarrassing. I don't *think* I had that typo there the whole time, but I can't swear to it.

Re: proc import 64-bit excel 2010 file into 64-bit SAS 9.4

Ksharp — Wed, 25 Jun 2014 13:06:50 GMT

First ,download PCFILESERVER from support.sas.com by searching download pcfileserver . After installing it , you can use dbms=xlsx to import excel2010.