Can SAS import a specific range of cells from a workbook using the libname syntax? If so can it be done with non-standard tab names that contain punctuation, etc.?
Yes. you can. libname x excel 'c:\temp\temp.xls' ; data want; set x.'Sheet1$'n(dbsastype=(g='char(10)' x='numeric')); run;
Yes, with the Excel engine, the syntax is:
libname xl Excel "c:\yourdir\yourFile.xlsx" access=readonly header=no;
data test;
set xl.'Sheet1$A1:Z50'n;
run;
Thanks for the suggestion - within this - can I predefine columns as character and numeric? Can this be done if the columns are blank?
With the Excel engine, no (things might be different with the new xlsx engine, which I haven't tested.) The type of data columns is based on a scan to the first few lines of data. Assigning a format, even to empty cells, is often enough to get the correct column type.
Yes. you can. libname x excel 'c:\temp\temp.xls' ; data want; set x.'Sheet1$'n(dbsastype=(g='char(10)' x='numeric')); run;
Another related question,
I have imported xls workbooks (the same one) a number of times sucessfully and now I am getting the following message:
ERROR: File _IMEX_.'a$a27:r3000'n.DATA does not exist.
ERROR: Import unsuccessful. See SAS Log for details.
It does not happen all the time but sometimes. Any thoughts as to the cause? Network communication issues, perhaps?
How did you import Excel file ? PROC IMPORT or LIBNAME ?
What is your code ?
I am currently using a PROC IMPORT step wtih SAS options to indicate which columns are numeric.
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.