Watch this Ask the Expert session to learn how to use SAS code to read and write Microsoft Excel files as if they were data sets in a library. It’s an easy way to get well-formed Excel data into your SAS process and to share results with colleagues.
Watch the webinar
You will learn how to:
- Read the contents of a spreadsheet into a new SAS data set.
- Control how much data you retain.
- Create and update XLSX files.
The questions from the Q&A segment held at the end of the webinar are listed below and the slides from the webinar are attached.
Q&A
Note: there were so many questions during this webinar, and I'm going to answer as many as I can here. It may take a couple of iterations to catch them all so check back over the next few days for updates.
Do we need to be licensed for XLSX engine?
You do need SAS/Access to PC files which is an additional license beyond Base SAS. In practice, most SAS users have this. And, as I mentioned with SAS Viya, those access engines are included already. If this engine was not licensed or installed, you would receive an error message when you tried to use it. The engine is available in SAS On-Demand for Academics, which is a free environment that anybody can access and play with. ODS EXCEL is part of Base SAS, no additional license needed.
How do I import multiple excel files all at the same time?
PROC IMPORT can deal with just one Excel file. So, sometimes what people do is they, if you need to do it in in a group, write a SAS macro to iterate through all the Excel files that are in a particular folder and import them. So, it requires a little bit more programming structure to make that happen, but there's not really a different way to do it at the moment. You can use a combination of libname, XLSX and PROC IMPORT to programmatically discover what's in a workbook and then PROC IMPORT to pull in what you want. But, there’s no wildcard support like we have in DATA step, where we can INFILE a collection of CSV files.
When I use use Proc Import on Excel files with a lot of text, SAS will most of the time truncate my variables. What can I do to import everything?
What happens is SAS tries to guess how long a variable column should be as it’s reading in that data, and it's possible that it doesn't do it right. In general, the DBMS=XLSX method should do a good job with this. For other methods (EXCEL or EXCELCS) there's a SCANTEXT option which basically tells PROC IMPORT to read it all and then decide how long the variable should be. There's also a TEXTSIZE option which allows you to override what's detected and optionally truncate to a max length.
Is there a way to make a read only excel file?
No, not using these methods in straight SAS code. Excel supports protecting content in in a workbook using a password as well as using making something read-only. But, there’s not a SAS syntax mechanism for making that happen. That would have to be a post-process step that you would have to apply once you got the data out to Excel.
Does proc export have a range option?
I don't think so, at least not with the XLSX options. If you want to export just a portion of the data to a sheet, add a WHERE= data set option in the PROC EXPORT statement.
When exporting a column that looks like numerals but is text with possible leading zeroes, will Excel preserve the character format and retain the leading zeroes? Or will Excel "smartly" convert to numerals, and change, say, text "007" to numeral 7?
Yeah, the classic example is zip codes. I know from my youth, I used to watch Zoom all the time, so I know that the area code for Boston MA is 02134. When you send that value to Excel, it's going drop the 0. So, there's two ways you can help force this. One is, in SAS, you can convert a ZIP code, or whatever that needs to be, to a legit character variable, and then when it is exported should be a character variable in Excel. The other is, in ODS Excel, you have a lot of control over how a variable is formatted. You could specify Excel-specific directives to apply a specific Excel format or formula or whatever (and this is all documented within the ODS Excel documentation) to say, I need to convert this SAS thing to this native Excel-style of representation. And so, that gives you the ultimate control over how that works, and so, that's better if that's your goal. Example:
proc print data=one;
var name;
var account / style(data)={tagattr="format:@"};
format account z6.;
run;
Is there a limit to how big the files can be with ods excel? I sometimes get errors that it's too big and can't be processed.
There are a couple of limits. There's a limit for Excel itself. I think many people know that the XLSX format can support a lot more data than its predecessor, the XLS format, but still is limited to approximately 1,000,000 or so rows per sheet. I did mention that ODS Excel is a little bit more verbose in terms of what it sends to the spreadsheet, because it's got all that formatting information. Beyond that, there may be this possibility that if you have a very, very large file from ODS Excel that there may be some kind of limitation, probably related to a sort of file system limitation as well.
When we're dealing with large files that hit that limitation, I might wonder, OK, does it need to be that large? If it's going to be that large, how useful is it later on downstream? But, people's requirements are their requirements, and I know we can't always get around that.
How I can read a excel sheet that has a password? I have to create a copy without a password before I use the proc import. Do you have a better way to do it?
I don't. LIBNAME XLSX and PROC IMPORT DBMS=XLSX methods do not support passwords. You're correct, you'd have to create an unlocked version to process in SAS. Where it's an extra sort of Excel-specific feature that you really need Excel to deal with it.
Is it a problem, if the data in one EXCEL column have different formats (i.e. with decimals and without)?
If we're talking about importing it, SAS will read it like a number and it will be a numeric value which will benefit from the position that SASS has for a numeric value. And then, how it appears in SAS is kind of up to you as to what SAS format you might apply. They'll be a default one when the value is right in, but you can change it later using proc datasets or another procedure to apply a different format if you want it to appear differently and more standardized. But, it doesn't indicate a data integrity problem.
If my final product needs to be a pdf, is there a way to use sas to convert a batch of excel files to pdfs? I am using ODS Excel and Proc Export to generate reports but then manually converting them to pdfs once they are generated.
Of course, SAS can create PDFs using ODS PDF. There's a destination for that, so that may save a step. I guess it depends on what that PDF needs to be. If the way that Excel generates a PDF from its spreadsheet-oriented output is what your final output needs to be, then maybe you won't be able to skip that. But, if you just need the data to be in PDF, then yes, you can just use ODS PDF from SAS and skip the Excel destination and go right to that. But, given the hoops that whoever asked the question is going through right now, I'm guessing that they are relying on whatever Excel does to create a PDF as their final output. And, they would have to do some research to figure out whatever SAS can generate, which is a lot, SAS ODS PDF is very flexible and has a lot of options to see if that is a possibility to just do that directly.
Recommended Resources
Using LIBNAME XLSX to read and write Excel files
How to use SAS to read a range of cells from Excel
Using ODS EXCEL and PROC EXPORT to bundle Excel-based reports
Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars.
Click image to register for webinar