Even if you don't use Microsoft Office 2007, you might have noticed more ".xlsx" files floating around lately. Perhaps you've been sent one or two that you can't open. XLSX is one of the new Microsoft Excel 2007 file formats. (Others include XLSB and XLSM.)

Like many software applications, SAS Enterprise Guide 4.1 cannot read these new file formats directly. The technology that we use to read Microsoft Excel files (and Microsoft Access files, which now sport an ".accdb" extension) was not updated to handle the fancy new formats produced by Microsoft Office.

One workaround is to ask your friends/colleagues/data suppliers to "please save these data files in a good old-fashioned XLS file that I can actually use." But if you cannot bear the shame of being "so, like, 2003-ish", there is another way to open these new files.
The key to solving this? SAS Enterprise Guide can open data sources using ODBC. It's ironic that I'm writing about this, since I am on record as recommending against using ODBC directly -- it's not the most efficient method of bringing data to SAS because it pipes third party data to your SAS server via your Windows client. However, in this case it's a handy enabling technology.

The steps:

1. First, you need the Microsoft Office 2007 data access providers/drivers.
If you have Microsoft Office 2007, you probably already have these drivers. But if you don't, you can download them from Microsoft and install them -- no Microsoft Office products needed.

2. In SAS Enterprise Guide, select File->Open->ODBC...
You'll see an ominous warning that tells you this is not going to be efficient, that you should use SAS/ACCESS libraries, that you should perhaps go to lunch while this is processing. It's a valid warning, but applies more to folks who use ODBC to connect to humongous data stores such as Oracle or Teradata. Since you trying to open smaller data sources (typical for Microsoft Excel and Access), the warning doesn't apply...as much.

3. The Select Data Source window displays. This is where you will define your DSN (database source name) for ODBC access. Click New... to begin.

4. In the Create New Data Source window, you'll see a list of available drivers. You'll have to select the correct one for the file type you want to read.

For Microsoft Excel 2007, find the one named "Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)". For Microsoft Access 2007, find the one named "Microsoft Access Driver (.mdb, .accdb)". Then click Next.

5. In the next page of the Create New Data Source window, give your DSN a name.
This will be saved on your computer so that you can easily get to this definition again later. Click Next, then click Finish.

6. Now you'll see a "setup" window where you can name the file you want to read.

For Microsoft Excel, you'll see the ODBC Microsoft Excel Setup window. Click Select Workbook to find your workbook file.

For Microsoft Access, you'll see the ODBC Microsoft Access Setup window. Click Select to find your accdb database file.

Once you've pointed the driver at your data file, click OK.

If your data file has multiple tables or worksheets, SAS Enterprise Guide will prompt you to select which one you want to open. If you have just one table, then it will open automatically.

One more tip for efficiency: in order for SAS to use the data that you've opened, SAS Enterprise Guide will have to move a copy of the data to the SAS session. If your data is large this might take a few moments (or longer). You can exert some control over the process by right-clicking on the data item in the process flow and selecting "Export->As a Step in Project". That effectively imports the data into a library within your SAS session, and then you can run queries and tasks on it from there.