| This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post. |
I hope that the following statement is not too controversial…but here it goes: Microsoft Excel is not a database system.
That is, I know that people do use it as a database, but it’s not an application that supports the rigor and discipline of managing data in the same way that “real” database systems do. Even Excel’s more boring and less popular cousin, Microsoft Access, supports the discipline of database management much better than Excel.
Still, Excel spreadsheets are widely used because they are so flexible and can contain rich content, including charts and fancy formatting. These might be assets when it comes to creating compelling output, but it doesn’t help a bit when your task is to analyze the data.
That’s why the first step to analyzing spreadsheet data within SAS is to import the data into a SAS data set. The Import process allows you to impose the oft-needed discipline to the spreadsheet data “on the way in” to your SAS environment. For example, you can:
- Select the data sheet, or an exact range within a sheet, that you want to import
- Apply formatting rules to the data values so that they are treated appropriately (as character, numeric, date or time values, for example)
- Control the field names so that you have SAS-friendly variable names in the resulting data set
Done properly, these steps will reduce the amount of “clean up” you need to perform before you can proceed with analysis.
The Import Data task within SAS Enterprise Guide (menu: File->Import Data) can lead you through this easily. For well-organized data in spreadsheets, the default settings are usually acceptable and you can simply review them and click Finish with no tweaking. For spreadsheet data that are less organized, the level of control that the wizard offers can help a lot.
We often get questions about how the import process works within SAS Enterprise Guide. For example, does it require SAS/ACCESS to PC Files? Does it generate a SAS program that you can reuse in batch code or in a stored process? Here are the answers:
SAS Enterprise Guide does not require SAS/ACCESS to PC Files in order to import Microsoft Excel content. SAS Enterprise Guide uses local Windows components to read the data from spreadsheets and then transforms them into a text-based format that SAS can import via DATA step. (Note: the same is true when importing Microsoft Access databases, Lotus worksheets, or even Paradox files.)
This means that when the step is complete you have your data in SAS, but you don’t have a SAS program that represents the entire process. SAS Enterprise Guide does some of the work behind the scenes. That’s convenient if you don’t have SAS/ACCESS to PC Files on your SAS server. It’s less convenient if you are trying to build a standalone batch program.
SAS Enterprise Guide can use SAS/ACCESS to PC Files if you want. If you do have SAS/ACCESS to PC Files on your Windows SAS server, you can check a box in the Advanced Options of the Import Data task and get SAS Enterprise Guide to generate a working program that uses PROC IMPORT with DBMS=EXCEL. This is similar to how the Import Data wizard works in the SAS display manager environment. However, there are a few requirements:
- The Excel file must reside in the file system of the SAS server (your local PC if using local SAS, or the remote Windows system if your SAS workspace is on a different machine).
- This is supported only when connecting to SAS on Windows. It is possible to use SAS/ACCESS to PC Files on a Unix system, but it requires a PC Files Server and the DBMS=EXCELCS option, which the Import Data task does not generate for you.
If all of those planets align for you, then you can use the Import Data task to create a reusable SAS program.
Exporting SAS data in Excel format does not require or use SAS/ACCESS to PC Files. Again, SAS Enterprise Guide uses the Windows-based APIs to create XLS files (or XLSX files with this custom task), and PROC EXPORT is not used for this. If you need the step to happen inside a SAS program, you can write your own PROC EXPORT step in a program node.
I hope that this clears up some of the questions and misconceptions we encounter. But if this post doesn’t do it for you, let me know in the comments.
| This post was kindly contributed by The SAS Dummy - go there to comment and to read the full post. |