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
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.
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
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.
So, for the benefit of others (like me ) what was wrong? - PG
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
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
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.
First ,download PCFILESERVER from support.sas.com by searching download pcfileserver . After installing it , you can use dbms=xlsx to import excel2010.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.