Proc Import fails to import Excel Sheet Range in SAS 9.4 (64-bit)

[SifuPineapple]

Hi,

I am upgrading a program to update, optimize and  work in SAS 9.4 (64-bit) as in the next month or so, the office IT people are removing SAS 9.2 and 9.3 (32-bit) from my machine.  My issue is that I have a 3 proc import statements (with DBMS=XLSX) with 3 distinctly different defined range that needs to be read in. The 3 defined ranges are in 3 different worksheets within one Excel workbook.  I double checked the Excel worksheet ranges and no matter which worksheet I am on, going to a given defined range moves me to the correct worksheet that the range is pointing to.  So the ranges exist in the Excel Workbook.Given below are the 3 lines of code that define the proc import statement.

proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aDocumenter replace; getnames=yes; range="ToDocument"; run;
proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aSupprimer replace; getnames=yes; range="Documented"; run;
proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.dejaDocumenter replace; getnames=yes; range="Transitory"; run;

These lines of code run perfectly fine when I run the program in SAS 9.3 (32-bit).  While testing the first import statement, if I remove the range command, the proc import statement imports the whole Excel sheet without any errors in SAS 9.4 (64-bit).

When I run the import statements with the ranges, I get the following error:

       Couldn't find range in spreadsheet

       Requested input file is Invalid

       ERROR: Import unsuccessful. See SAS Log for details.

       Note: The SAS system stopped processing this step because of errors.

I am confused with the "See SAS log for details" in the error statement.  I mean I got the error for this issue by looking at the SAS log.

Just to clear everything up, I am using MS Office 2013 (32-bit) and Windows 7 Pro (64-bit).

SifuPineapple

[Reeza]

I love that error message, it makes absolutely no sense. 

Try a libname instead - check what the log says.

libname demo xlsx 'F:\LFS\AR_combo.xlsx';

proc datasets lib=demo list details;
run;

data aDocumenter;
set demo.ToDocument;
run;

data aSupprimer;
set demo.Documented;
run;

---

@SifuPineapple wrote:

Hi,

I am upgrading a program to update, optimize and  work in SAS 9.4 (64-bit) as in the next month or so, the office IT people are removing SAS 9.2 and 9.3 (32-bit) from my machine.  My issue is that I have a 3 proc import statements (with DBMS=XLSX) with 3 distinctly different defined range that needs to be read in. The 3 defined ranges are in 3 different worksheets within one Excel workbook.  I double checked the Excel worksheet ranges and no matter which worksheet I am on, going to a given defined range moves me to the correct worksheet that the range is pointing to.  So the ranges exist in the Excel Workbook.Given below are the 3 lines of code that define the proc import statement.

proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aDocumenter replace; getnames=yes; range="ToDocument"; run;
proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aSupprimer replace; getnames=yes; range="Documented"; run;
proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.dejaDocumenter replace; getnames=yes; range="Transitory"; run;

These lines of code run perfectly fine when I run the program in SAS 9.3 (32-bit).  While testing the first import statement, if I remove the range command, the proc import statement imports the whole Excel sheet without any errors in SAS 9.4 (64-bit).

When I run the import statements with the ranges, I get the following error:

       Couldn't find range in spreadsheet

       Requested input file is Invalid

       ERROR: Import unsuccessful. See SAS Log for details.

       Note: The SAS system stopped processing this step because of errors.

I am confused with the "See SAS log for details" in the error statement.  I mean I got the error for this issue by looking at the SAS log.

Just to clear everything up, I am using MS Office 2013 (32-bit) and Windows 7 Pro (64-bit).

SifuPineapple

---

[SifuPineapple]

Hi Reeza,

Thanks for looking into this so promptly.  However, what you suggested is also not working.  Firstly, the software crashes out on the proc datasets step you mentioned because it does not recognize "list".  Once I removed the "list" the same error shows up:

      Couldn't find sheet in spreadsheet

      Requested input file is invalid

      ERROR: File demo.ToDocument.data does not exist.

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

There is one positive thing though.  The comment, "See SAS Log for details", no longer shows up.

Sifu 

[Reeza]

What is the output from proc datasets? It would have been in the log as well - please show that output. It should show the data.

1. Can you verify that your file is still correct and hasn't been damaged.
2. What exact version of SAS are you using - ie 9.4 TS1M6?

[SifuPineapple]

The version of SAS i am using is SAS 9.4 TS Level 1M3,

In the proc datasets command when I use "list" underlines list and a says:

      ERROR: Syntatx error , expecting on of the following: ;, ALTER, DD, DDNAME, DETAILS, FORCE GENNUM,KILL, LIB, LIBRARY, MEMTYPE, MT, MTYPE, NODETAILS, NOFS, NOLIST, NOPRINT, NOWARN, PROTECT, PW, READ.

      ERROR: The option or paramter is not recognized and will be ignored.

When I run proc datasets with the list command, I get 2 charts output in Results Viewer:

The first chart:

                                                                  Directory

Libref                             DEMO

Engine                           XLSX

Physical Name              F:\LFS\AR_Combo.xlsx

Por

The second chart:

#                        Name                                      Member type    Obs, Entries or Indexs       Vars                  Label

1   DEFINITIONS                                                DATA                                                            7

2   DOCUMENTED - DOCUMENTéS                 DATA                                                        161

3   HISTORIQUE                                                 DATA                                                          13

4   INFORMATIONS                                            DATA                                                            3

5   TO DOCUMENT - À DOCUMENTER            DATA                                                         161

6    TRANISTORY - ÉPHéMèRES                      DATA                                                         160

As for the Excel sheet itself, like I mentioned, it works perfectly with SAS 9.3 so there is no change to sheet.  None the less I am going to recreate the Excel sheet from scratch and see if does anything.

SifuPineapple

[Tom]

Notice how the names do not match the names you tried to use?

If you want to use those names with spaces in them as member names you need to set the VALIDMEMNAME option to EXTEND and use name literals.

[Reeza]

LIST was my mistake, remove it, that's fine. But the named ranges in the Excel sheet don't seem to match what you think they are. And yes, the XLSX engine has changed significantly between 9.3 and 9.4 - mostly in good ways. But it does mean you may have to change your code.

[Reeza]

I tested this in 9.4 and I'm getting the same behaviour you are, XLSX does not seem to recognize named ranges. I know PCFILES does though, so maybe you can use that approach?

In the end this worked for me. I'm attaching the excel workbook, can you run this exact code on that workbook and let me know what happens. It is NOT listed in the PROC DATASETS output though.

libname test xlsx  '/home/fkhurshed/my_courses/FriendsOfSAS/Delete.xlsx';

proc datasets lib=test details;
run;quit;

data myData;
set test.myRange;
run;

---

@SifuPineapple wrote:

Hi,

I am upgrading a program to update, optimize and  work in SAS 9.4 (64-bit) as in the next month or so, the office IT people are removing SAS 9.2 and 9.3 (32-bit) from my machine.  My issue is that I have a 3 proc import statements (with DBMS=XLSX) with 3 distinctly different defined range that needs to be read in. The 3 defined ranges are in 3 different worksheets within one Excel workbook.  I double checked the Excel worksheet ranges and no matter which worksheet I am on, going to a given defined range moves me to the correct worksheet that the range is pointing to.  So the ranges exist in the Excel Workbook.Given below are the 3 lines of code that define the proc import statement.

proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aDocumenter replace; getnames=yes; range="ToDocument"; run;
proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aSupprimer replace; getnames=yes; range="Documented"; run;
proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.dejaDocumenter replace; getnames=yes; range="Transitory"; run;

These lines of code run perfectly fine when I run the program in SAS 9.3 (32-bit).  While testing the first import statement, if I remove the range command, the proc import statement imports the whole Excel sheet without any errors in SAS 9.4 (64-bit).

When I run the import statements with the ranges, I get the following error:

       Couldn't find range in spreadsheet

       Requested input file is Invalid

       ERROR: Import unsuccessful. See SAS Log for details.

       Note: The SAS system stopped processing this step because of errors.

I am confused with the "See SAS log for details" in the error statement.  I mean I got the error for this issue by looking at the SAS log.

Just to clear everything up, I am using MS Office 2013 (32-bit) and Windows 7 Pro (64-bit).

SifuPineapple

---

[SifuPineapple · Accepted solution]

Tom, thank your for your input. 

It appears that the name variable in the proc datasets output is not pointing to the range names but in fact the worksheet names in the Excel Workbook.  Since I work in an bilingual capacity, the names for each sheet are provided in English and French.  I double checked the range names and the range names in the Excel sheet match the range names in the SAS code. (I double checked by simply copying and pasting the range names in the Excel sheet directly into the SAS code.)

I also double checked the valid names issue and it seems like you have hit the issue directly on the head - it is a worksheet names length/spaces/special character issue.  Once I removed the French names (with their associated special characters), special characters and spaces from the worksheet name, SAS immediately created the data sets exactly the way I wanted them without any issues.

So now I would like to thank Reeza and Tom for helping to resolve the problem.  Your prompt responses have exceeded expectations. 🙂

Have a great week!!

SifuPineapple