SAS export to Excel, unable to read back into SAS

Question

[NOTE: I inserted a line below and added a detailed description of what is going on for me. I left my original description because Tom's answer is based on it.]

Using SAS 9.4 (32-bit) and Excel 2013 (32-bit) under Win10Pro.

In SAS, I created a new Excel worksheet in an existing workbook using the following code:

proc export data=sashelp.air file="C:\Users\user1\Desktop\test1.xlsx" 
     dbms=XLSX replace;
    sheet="AirlineData";
run;

I closed SAS, opened the workbook in Excel, and saw my original worksheet plus the new AirlineData sheet with the expected data in it (header plus 144 data rows). So far, so good.

I closed Excel, opened SAS, and executed the following SAS statements:

options validmemname=extend;
libname desktop "C:\Users\user1\Desktop\test1.xlsx";

I opened SAS Explorer and navigated to library "Desktop" and saw three worksheets:

• AirlineData
• AirlineData$
• Sheet1$

When I double-clicked on Sheet1$, I saw the original data, but when I double-clicked on either of the other names, all I saw were empty tables, something like this:

    F1 F2
  1
  2
  3

The cells were empty, but there were 144 observations. The header row was ignored.

I executed PROC PRINT DATA=desktop.airlinedata and saw the same thing in the results viewer: variables named F1 and F2 with 144 empty observations.

However, when I cleared the libref and ran the following, I got a SAS dataset with the expected data (two variables, 144 observations):

proc import datafile="C:\Users\user1\Desktop\test1.xlsx" dbms=xlsx OUT=testit
     replace;
    sheet="AirlineData";
run;

So it appears that when I create a worksheet with PROC EXPORT, I cannot use the worksheet through a libref.

Am I doing something wrong?

---

[New description]

Summary: The issue is how the EXCEL and XLSX engines work when creating a new worksheet in an existing workbook. It may be debatable whether the two engines should be interchangeable, but it seems to me that if SAS is creating a new worksheet correctly, it should be able to read it with either engine.

So what actually happens is if you create the new worksheet with PROC EXPORT using the XLSX engine but use LIBNAME <libref> [EXCEL] <path\file.xlsx> to read it via the EXCEL engine, then methods like 'PROC PRINT' or a 'DATA' step will not read it correctly. However, if you create it with PROC EXPORT using the EXCEL engine, SAS can read it using a LIBNAME statement with either the XLSX or EXCEL engine.

Step 1:

Workbook test.xlsx was created in Excel 2013 with three rows of four single-digit numbers.

proc export data=sashelp.air file="C:\Users\user1\Desktop\test.xlsx" 
     dbms=XLSX replace;
    sheet="AirlineData";
run;

Close SAS. Excel displays the AirlineData worksheet in a normal manner. Close Excel.

Reopen SAS, execute:

1    PROc import datafile="C:\Users\user1\Desktop\test.xlsx" dbms=xlsx OUT=xx replace;
2        sheet="AirlineData";
3    run;
NOTE: WORK.XX data set was successfully created.
NOTE: The data set WORK.XX has 144 observations and 2 variables.

Dataset XX appears to be a normal SAS dataset with the correct airline data.

Now execute:

6    options validmemname=extend;
7    libname xldata XLSX "C:\Users\user1\Desktop\test.xlsx";
NOTE: Libref XLDATA was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: C:\Users\user1\Desktop\test.xlsx

The SAS Explorer under library xldata shows "SHEET1" and "AIRLINEDATA," but the SAS viewer will not open either one (problem described earlier and since reported to SAS support).

However, PROC PRINT and a DATA step print and copy the data from both worksheets as expected.

Now execute:

8    options validmemname=extend;
9    libname xldata "C:\Users\user1\Desktop\test.xlsx";
NOTE: Libref XLDATA was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: C:\Users\user1\Desktop\test.xlsx

Note EXCEL is the engine.

The SAS Explorer under library xldata shows "Sheet1$." "AirlineData," and "AirlineData$."

• The SAS viewer opens Sheet1$ with no problems.
• The SAS viewer says AirlineData$ has 0 observations. It displays a single variable (F1) with no observations.
• The SAS viewer displays variables F1 and F2 with 144 apparently empty observations for AirlineData.

PROC PRINT also finds no observations in AirlineData$ and prints 144 empty observations from AirlineData.

DATA step also finds no observations in AirlineData$ and copies 144 empty observations from AirlineData.

Step 2:

Excel workbook test2.xlsx is a duplicate of the original test.xlsx (created by copying in Windows).

28   LIBNAME xldata clear; * Just in case;
NOTE: Libref XLDATA has been deassigned.

29   proc export data=sashelp.air file="C:\Users\user1\Desktop\test2.xlsx"
30        dbms=EXCEL replace;
31       sheet="AirlineData";
32   run;
NOTE: "AirlineData" range/sheet was successfully created.

Close SAS. Excel displays the AirlineData worksheet in a normal manner. Close Excel.

Reopen SAS, execute:

1    PROc import datafile="C:\Users\user1\Desktop\test2.xlsx" dbms=EXCEL OUT=xx replace;
2        sheet="AirlineData";
3    run;
NOTE: WORK.XX data set was successfully created.
NOTE: The data set WORK.XX has 144 observations and 2 variables.

Dataset XX appears to be a normal SAS dataset with the correct airline data.

Now execute:

4    options validmemname=extend;
5    libname xldata xlsx "C:\Users\user1\Desktop\test2.xlsx";
NOTE: Libref XLDATA was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: C:\Users\user1\Desktop\test2.xlsx

The SAS Explorer under library xldata shows "SHEET1" and "AIRLINEDATA," but the SAS viewer will not open either one (same problem as above).

As above, PROC PRINT and a DATA step print and copy the data from both worksheets as expected.

Now execute:

12   options validmemname=extend;
13   libname xldata "C:\Users\user1\Desktop\test2.xlsx";
NOTE: Libref XLDATA was successfully assigned as follows:
      Engine:        EXCEL
      Physical Name: C:\Users\user1\Desktop\test2.xlsx

Note EXCEL is the engine.

The SAS Explorer under library xldata shows "Sheet1$." "AirlineData," and "AirlineData$." The SAS viewer opens all three sheets with no problems.

Also, PROC PRINT and a DATA step print and copy the data from all three worksheets as expected.

Answer 1

I reported these issues to SAS support.

They confirmed that the first issue (above the line in the OP) is strictly related to viewing Excel worksheets using the XLSX engine. They acknowledged that the XLSX engine has limited functionality at present.

For the second issue (below the line in the OP), support thought it represented a bug. They said they would contact R&D about it.

Answer 2

Make sure to use the XLSX engine in your libname statement.

This code works for me, but it is creating the XLSX file from scratch, not using one that was already created by some other software.

Make an XLSX file and close it.

libname out xlsx '~/test/test1.xlsx';
data out.class ; set sashelp.class; run;
libname out ;

Open it again and add a new table.

libname out xlsx '~/test/test1.xlsx';
data out.air; set sashelp.air; run;
libname out;

Open it again and read the data out.

libname in xlsx '~/test/test1.xlsx';
proc copy inlib=in outlib=work;
run;
libname in ;