Hello,
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.
Thanks!
Try using the range option:
RANGE='A13:D113';
Adjust the range to fit what you want. For 25 columns, it would be A <whatever>: Y<whatever
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.
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.
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";
data STuffInTest;
set xl.'Sheet1$'n; /* must use literal for sheet name */
run;
libname xl clear;
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)
Thanks.
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!
SAS 9.4 can use engines to read/write XLSX and XLS file directly on both Unix and Windows.
libname mylib xlsx 'myfile.xlsx';
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.