10-31-2016 11:34 AM
When I use proc import to read excel .xlsx file.
there are total 25 column in the excel.but after I read it into SAS, it cost me longer time and read a 255 column SAS data set ,
230 columns are empty. I don't know what problem is this. this happend on some special excel file while other excel files don't have problem.
I can't see any different among these excel files.
10-31-2016 12:56 PM
Note that Excel will occasionally treat a column as having "something" even after deleting the data in the columns.
You didn't say how you read the data into SAS but many of the approaches rely on Excel telling SAS about the contents. When Excel has "ghost" columns then they get imported.
One thing you might try is exporting the file to CSV and see if you get hundreds of commas as the end of each line when you look at the exported file with a text editor or something like Wordpad. If so, then you have ghost columns.
Excel will also do this with rows.
10-31-2016 02:05 PM
There's a function in Excel that moves the cursor to the "last" cell, which will show you what Excel thinks is the maximum used column and row.
10-31-2016 09:50 PM
Have you tried using the libname approach? I created a bunch of columns, then deleted the data from them to attempt to simulate your issue. I saved the file as test.xlsx. make sure you clear the libname connection or you cannot access the excel file. more info is provided in the base Sas 1 course.
libname xl pcfiles path="&Mypath\test.xlsx";
set xl.'Sheet1$'n; /* must use literal for sheet name */
libname xl clear;
11-02-2016 08:37 AM
The libname method is very good but I have SAS 9.4(even 9.3)) which not support this method.(I knew only 9.2 support this)
11-02-2016 02:23 PM
I tried with your mothod, it works! you add pcfiles engine so that works!
while my old method not work at 9.4 but works at 9.2:
LIBNAME Myfile1 "C:temp\test.xlsx";
Thank you very much!
11-02-2016 02:29 PM - edited 11-02-2016 02:30 PM
SAS 9.4 can use engines to read/write XLSX and XLS file directly on both Unix and Windows.
libname mylib xlsx 'myfile.xlsx';