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?
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.
On Tue, Jul 28, 2009 at 11:44 AM, Ya Huang <email@example.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:
> 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
> 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?