Excel Import

Reply
Frequent Contributor
Frequent Contributor
Posts: 109

Excel Import

[ Edited ]

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.

Frequent Contributor
Frequent Contributor
Posts: 109

Re: Excel Import

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.

Super User
Posts: 11,144

Re: Excel Import


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.

Frequent Contributor
Frequent Contributor
Posts: 109

Re: Excel Import

[ Edited ]

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??????? 

Super User
Posts: 11,144

Re: Excel Import

"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.

 

 

Frequent Contributor
Frequent Contributor
Posts: 109

Re: Excel Import

[ Edited ]

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
Frequent Contributor
Frequent Contributor
Posts: 109

Re: Excel Import

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...

Frequent Contributor
Frequent Contributor
Posts: 109

Re: Excel Import

[ Edited ]

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

Super User
Posts: 11,144

Re: Excel Import

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.

Ask a Question
Discussion stats
  • 8 replies
  • 188 views
  • 2 likes
  • 2 in conversation