Date:         Tue, 28 Jul 2009 11:52:23 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: proc import read excel file, how to specify range?
Comments: To: Ya Huang <ya.huang@amylin.com>
In-Reply-To:  <200907281644.n6SAnwa2030045@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

Specify the range using excel terminology; so "A1:G21" would be your range.
You could set the range in Excel to be a named range ("SASDATA" or something
) and then refer to that instead of a range.

You might try LIBNAME, it makes things a bit more clear with named ranges.

libname mylib excel 'file.xls';

Then any named range will be easily seen in the library.

-Joe

On Tue, Jul 28, 2009 at 11:44 AM, Ya Huang <ya.huang@amylin.com> wrote:

> Hi there,
>
> First of all, I use v9.2. But I can't find any reference in v9.2
> online doc regarding how to specify range. I do still have v8.2
> online doc installed locally and I found the following:
>
> RANGE=range-name|absolute-range
> subsets a spreadsheet by identifying the rectangular set of cells to import
> from the specified spreadsheet. The syntax for range-name and absolute-
> range is native to the file being read. The range-name is the name that is
> assigned to a range address within a spreadsheet. The absolute-range
> identifies the top left cell that begins the range and bottom right cell
> that ends the range. The beginning and ending cells are separated by two
> periods. For example, C9..F12 specifies a cell range that begins at cell
> C9, ends at cell F12, and includes all the cells in between. If you do not
> specify RANGE=, PROC IMPORT reads the entire spreadsheet. You may replace
> the equals sign with a blank. Restriction: You cannot use absolute-range
> with Excel 97 spreadsheets
>
> So I tried this code:
>
> 1          proc import datafile="C:\temp\junk.xls"
> 2               out=xx
> 3               replace;
> 4               sheet='YY';
> 5               range="A1..G21";
> 6               getnames=yes;
> 7          run;
>
> WARNING: SHEET name will be ignored if conflict occurs with RANGE name
> specified.
>
> ERROR: File _IMEX_.'A1..G21'n.DATA does not exist.
> ERROR: Import unsuccessful.  See SAS Log for details.
>
> I also tried without the quote, it didn't work either.
>
> So my questions are:
> 1. How to specify the range.
> 2. Is there a way to automatically set the range, so that proc import
>   read the sheet up to the last rows with data?
>
> Thanks
>
> Ya
>