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

[KHaavik]

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

[ballardw]

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.

[KHaavik]

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

[KHaavik]

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.

[PGStats]

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

[KHaavik]

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

[PGStats]

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

[KHaavik]

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.

[Ksharp]

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