6月 212018

I've said it before: spreadsheets are not databases. However, many of us use spreadsheets as if they were databases, and then we struggle when the spreadsheet layout does not support database-style rigor of predictable rows, columns, and variable types -- the basic elements we need for analytics and reporting. If you're using SAS to read data from Microsoft Excel, what can you do when the data you need doesn't begin at cell A1?

By design, SAS can read data from any range of cells in your spreadsheet. In this article, I'll describe how to use the RANGE statement in PROC IMPORT to get the data you need.

With SAS 9.4 and later, SAS recommends using DBMS=XLSX for the most flexibility. It works on all operating systems without the need for additional components like the PC Files Server. Your Excel file must be in the Excel 2007-or-later format (XLSX). You do need a licence for SAS/ACCESS to PC Files. (Just learning? These DBMS=XLSX techniques also work in SAS University Edition.)

If your Excel data does not begin in cell A1 (the default start point for an import process), then you can add a RANGE= value that includes the specific cells. The easiest method is to use a Named Range in Excel to define the exact boundaries of the data.

How to add a Named Range

To define a named range in Excel, highlight the range of cells to include and simply type the new name of the range in the Name Box:
Excel named range

Then save the Excel file.

Then to import into SAS, specify that range name in the RANGE= option:

proc import datafile="/myprojects/myfile.xlsx"

Using Excel notation for a cell range

What if you don't know the range ahead of time? You can use PROC IMPORT to read the entire sheet, but the result will not have the column headers and types you want. Consider a sheet like this:

Excel with floating data

This code will read it:

proc import datafile="/myprojects/middle.xlsx"
 out=mid dbms=xlsx

But the result will contain many empty cells, and the values will be read as all character types:

Excel naive import

With additional coding, you can "fix" this result in another pass using DATA step. Or, if you're willing to add the RANGE option with the Excel notation for the specific cell ranges, you can read it properly in the first pass:

proc import datafile="/myprojects/middle.xlsx"
 out=mid dbms=xlsx
 range="Sheet1$E7:K17" ;

How to "discover" the structure of your Excel file

You can also use LIBNAME XLSX to read entire sheets from Excel, or simply as a discovery step to see what sheets the Excel file contains before you run PROC IMPORT. However, LIBNAME XLSX does not show the Excel named ranges.

On SAS for Windows systems, you can use LIBNAME EXCEL (32-bit) or LIBNAME PCFILES (64-bit) to reveal a little more information about the Excel file.

libname d pcfiles path="c:\myprojects\middle.xlsx";
proc datasets lib=d; quit;
/* always clear the libname, as it locks the file */
libname d clear;

Libname XLSX proc datasets

Note that DBMS=XLSX does not support some of the options we see in the legacy DBMS=XLS (which supports only old-format XLS files), such as STARTROW and NAMEROW. DBMS=XLSX does support GETNAMES (treats the first record of the sheet or range as the variable names). See the full reference for Excel file import/export in the SAS documentation.

The post How to use SAS to read a range of cells from Excel appeared first on The SAS Dummy.

11月 152016

Are you the lone-wolf SAS programmer in your company - managing the data, performing the analyses, and graphing the output for everyone else? And what's the only format they all know how to work with, and wish they had your output in? Let's face it ... that's probably an Excel […]

The post Send your SAS graphs to Excel, directly to Excel ... appeared first on SAS Learning Post.

9月 012015

“When building a predictive model, we find the JMP Pro interfaces to be very intuitive, allowing us to work closely with other JMP Pro users to build the model together.” -- Amy Clayman, Data-Driven Decisions Circle, VCE Beyond Spreadsheets is a blog series that highlights how JMP customers are augmenting […]

The post Beyond Spreadsheets: Amy Clayman, Voice Systems Engineering appeared first on JMP Blog.

8月 252015

“I love being able to tell my students that if they learn JMP, it will be a skill they can put on their resume that will set them apart from other applicants.” -- Mary Ann Shifflet, Professor, Romain College of Business, University of Southern Indiana Beyond Spreadsheets is a blog […]

The post Beyond Spreadsheets: Mary Ann Shifflet, University of South Indiana appeared first on JMP Blog.

8月 112015

“The software you use not only shapes what you learn from your data; it shapes the questions you ask!” -- Tom Treynor, Director, Zymergen The Beyond Spreadsheets blog series shows how JMP customers are augmenting their tools and processes for exploratory data analysis to make breakthrough discoveries. The series features […]

The post Beyond spreadsheets: Tom Treynor, Zymergen appeared first on JMP Blog.

8月 112015

While I've often written about how to get your SAS data to Microsoft Excel in some automated way, I haven't really addressed what's probably the most frequently used method: copy and paste. SAS Enterprise Guide 7.1 added a nifty little feature that makes copy-and-paste even more useful.

The new "Copy with headers" feature creates a tab-delimited version of your selected data cells, complete with a heading row that includes the column names. This is very convenient for copying into a new Microsoft Excel spreadsheet or other table structure (like a Google Docs spreadsheet). Note: this is different than the "copy data attributes" tip that I published a while back. That tip captures the column properties, but not the actual data values.

To get started, simply select the data that you want within the SAS Enterprise Guide data grid. The data cells must be contiguous, but you don't need to select all columns or all cells within the data set. With the selection active, right-click and select Copy with headers.

copy with headers
This action places the data values in tab-delimited form onto the Windows clipboard. The first line of the data will be the SAS variable names from your selected data. If you paste this into a text editor that shows a view of "special characters", you can see the tabs along with the end-of-line delimiters.

tab delimited content
When you paste the same content into Microsoft Excel, the Excel application knows how to automatically distribute these values into distinct columns. That's just what spreadsheet programs do.

paste into Excel
Microsoft Excel isn't the only app that can handle tab-delimited data in this way. You can paste the content into a Google Doc spreadsheet too.

paste into Google Docs
Or, you could simply paste into a text file as-is, and then save that file to read into a SAS program at a later time, bringing it full circle.

tags: excel, SAS Enterprise Guide

The post Copy data and column names from SAS Enterprise Guide appeared first on The SAS Dummy.

8月 042015

“Spreadsheets are familiar tools, which are relatively simple to use. However, the downside is that they result in fragmented thinking.” -- Ken Franklin, Performance Measurement Program Manager, Highway Division of Oregon Department of Transportation (ODOT) JMP customers use an array of tools and processes for exploratory data analysis to make […]

The post Beyond spreadsheets: Ken Franklin, Oregon Department of Transportation appeared first on JMP Blog.

7月 012015

The data you want to import into JMP often requires some manipulation before it’s ready to be analyzed in JMP. Sometimes data is arranged so that a row contains information for multiple observations. To prepare your data for analysis, you must restructure it so that each row of the JMP […]

The post How to stack data for a Oneway analysis appeared first on JMP Blog.

6月 242015

“I want my students focusing on statistical methods, not on software.” -- Bruce McCullough, Professor, LeBow College of Business, Drexel University Beyond Spreadsheets is a blog series that highlights how JMP customers are augmenting their tools and processes for exploratory data analysis to make breakthrough discoveries. We talk with JMP […]

The post Beyond Spreadsheets: Bruce McCullough, Drexel University appeared first on JMP Blog.

6月 092015

Here at JMP, we love pets. So we were thrilled to hear that a young scientist used our software to explore data about pet adoptions from local animal shelters. The project is adorably titled "Furever Friends." How young is this scientist? She is 10 years old, and her name is […]

The post Why are some dogs adopted faster than others? appeared first on JMP Blog.