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