I come across this error about once or twice a year for different projects. So I am going to try and understand its possible source.
I am trying to import an excel file '.xlsx'. I use the import wizard and everything is fine. I am currently going through a data cleaning phase, so when I notice a data anomaly or a formatting issue, I correct the source document. Then when I go to use the code from the import wizard again to check that the source issue has been fixed I get the following error. Examples of source document fixes that I am making are trivial (e.g., changing yes to Yes, etc.).
239 PROC IMPORT OUT= WORK.NIV_bust 240 DATAFILE= "X:\Pediatrics\NI V Asthma\Data\2017 January 240! Data 241 Files\Final NIV Dataset Jan_17.xlsx" 242 DBMS=EXCEL REPLACE; 243 RANGE="'Final NIV Data$'"; 244 GETNAMES=YES; 245 MIXED=NO; 246 SCANTEXT=YES; 247 USEDATE=YES; 248 SCANTIME=YES; 249 RUN; ERROR: Unable to open file X:\Pediatrics\NI V Asthma\Data\2017 January DataFiles\Final NIV Dataset Jan_17.xlsx. 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.03 seconds
To try and pre-empt possible questions, I am using SAS 9.4 probably maintenance 1 or 2 and I am also closing the excel file before trying to use the proc import code for the second time. The only way I can get the dataframe to re-import is to go through the whole PROC IMPORT process all over again, and it works with no problem. I commonly maintain a master data fille for a project in excel and reuse PROC IMPORTS code, similarly as mentioned above without any issues. So this is a sporadic issue.
P.S., I was not a fan of when SAS removed the PROC IMPORT code out of the Log, now it only prints it in the Log if there is an issue. In prior SAS versions it printed the generated PROC IMPORT code in the log everytime and I could Cut-N-Paste it into my editor window for future use.
@H wrote:
P.S., I was not a fan of when SAS removed the PROC IMPORT code out of the Log, now it only prints it in the Log if there is an issue. In prior SAS versions it printed the generated PROC IMPORT code in the log everytime and I could Cut-N-Paste it into my editor window for future use.
Note the the wizard should have an option to save the generated code in a location you specify.
Since your path to the that file looks like it is on a network drive or some other remote location intermittent issues like yours may be temporary network load, someone else with access to the file actually has it open or another process is running that lock access.
Correct, the Wizard will save the PROC IMPORT code in a file. I am familiar with this option/procoss, though in case a SAS Inc. person is reviewing these messages, I wanted to voice my dislike in the change where it is not displayed in the log that seed like if happened around 9.3. Now, back to my actual problem...
So check this out:
The source file of interest is saved in my personal server workspace, so no one else should ever have access to the file. That can be confirmed by examining the file properties.
The above issue only happens to certain files. The other 95% of files I want to import are fine and never have these issues. I have tried many things, such as renaming the source file and Cutting-N-Pasting its content into a new file, but whatever SAS doesn't like about the file sticks with it. The following is even more interesting.
So the piece of code generated from the PROC IMPORT works when it is intitially created. Though when I go to use the generated code (which you reference) it won't work. Though, if I just retyped the code verbatim in my Editor window it will work? Also, if I use the Import Wizard again that will work, but not its generated code, but reruning the originally produced code PROC IMPORT doesn't work.
So Imporrt wizard code works the first time when creating it, then not again, cut-N-pasting doesn't work. but retyping it in its entiety works as well as rerun the import wizard. Work = importing the file without the Log warning above???????
"Doesn't work" is a pretty vague description. Do you get errors? Code runs but not output? We might be able to help if we had log and any messages generated when the code is run.
Here is a long shot: is the saved code ever touched with another "editor" program, maybe actually a word processor? Some times non-code non-visible characters creep into the file, possibly unicode or different encoding than your session and cause problems. Since you say that typing from scratch works but when reusing a created file doesn't work it sounds like something is altering the code.
You can make direct suggestions for improvements or changes using the SASware Ballot Ideas area of the forum. That will get reviewed by SAS personnel and other users have the option to indicate support for the idea. Provide details as to why the behavior is an improvement or desireable other than "it is better". Other forum users may ask questions about the idea.
I appreciate your feedback. I get the above error that is listed in the first post, gray box. Below is what I get when I retype the syntax:
317 proc import out= work.niv_bust 318 datafile= "X:\Pediatrics\NI V Asthma\Data\2017 January 318! Data Files\Final NIV Dataset Jan_17.xlsx" 319 DBMS=Excel Replace; 320 RANGE="'Final NIV Data$'"; 321 GETNAMES=YES; 322 MIXED=NO; 323 SCANTEXT=YES; 324 USEDATE=YES; 325 SCANTIME=YES; 326 RUN; NOTE: WORK.NIV_BUST data set was successfully created. NOTE: The data set WORK.NIV_BUST has 168 observations and 41 variables. NOTE: PROCEDURE IMPORT used (Total process time): real time 1.06 seconds cpu time
As for my protocol:
-run Wizard, it creates the file and saves PROC IMPORT.
-double click on created SAS file with PROC IMPORT, opens in Enterprise, which I never use.
-I copy code and paste into my active Editor session in SAS 9.4.
-Copied code doesn't work.
-Retyped code works.
Side note, this process works 95% of the time with zero issues. Every once in awhile a data file of interest is stymied when pastiing code into my active SAS Editor session.
I am going to play around with some things now to see if the code will work if I skip the Enterprise step...
Alright, I appreciate everyones time. I have opened and created the PROC IMPORT everyway I could think possible.
Then I retyped the import and it work.
Next I deleted every line of a copied version of the PROC IMPORT until I found where the issue was.
Regardless of how I open the PROC IMPORT code, it is breaking the file location ext. See above code in the gray boxes. SAS is breaking the location in the file name destination between the words "Data" and "File". When I retype the extension on one line the code seems to work fine.
Side note, I was not creating that break, it was occuring when I would open the Import Wizard generated code in either Enterprise or in SAS 9.4.
So the work around solultion is to bring the spilled over line back up to the previous line. I will leave this thread open for awhile in case someone knows how to get rid of the issue. Interestingly enough, a maximized diplayed Editor also doesn't correct this. The reason I must come across this issue only a couple of times a year must be because most of my files aren't located so deep in subfolders. Hmm.
Any feedback would be appreciated.
Thanks,
H
A known issue with a variety of things when there are spaces in the path or file names.
The best solution is not to have the space in the path.
Second is when using the code to look at it and edit it correctly.
The ! you see a "line continues" generated by the code generator which for some ancient and sometimes very valid reasons limits generated code lines to 80 characters.
the code should look like this in the editor before submission (edited from the first post)
PROC IMPORT OUT= WORK.NIV_bust DATAFILE= "X:\Pediatrics\NI V Asthma\Data\2017 January Data Files\Final NIV Dataset Jan_17.xlsx" DBMS=EXCEL REPLACE; RANGE="'Final NIV Data$'"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;
It appears that the code when opened in the editor for submission would likely have looked like this prior to submission.
PROC IMPORT OUT= WORK.NIV_bust DATAFILE= "X:\Pediatrics\NI V Asthma\Data\2017 January ! Data Files\Final NIV Dataset Jan_17.xlsx" DBMS=EXCEL REPLACE; RANGE="'Final NIV Data$'"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;
Make sure the saved code looks right before running.
If you are often importing the same format file with the same range you might try using a FILEREF.
Start with
filename NIVin "X:\Pediatrics\NI V Asthma\Data\2017 January Data Files\Final NIV Dataset Jan_17.xlsx";
Use the import wizard but use the filename for the datafile name (or just replace in the code) to get:
PROC IMPORT OUT= WORK.NIV_bust DATAFILE= NIVin DBMS=EXCEL REPLACE; RANGE="'Final NIV Data$'"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;
If you save the filename statement, or re-execute with another file before the import then you shouldn't have that problem. Of course this will not work if the range changes though there may be ways around that as well.
I seldom use the wizard twice for the same structured data.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.