Date:         Tue, 29 Jul 2003 09:31:03 -0700
Reply-To:     Prasad S Ravi <prasad.s.ravi@HOUSEHOLD.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Prasad S Ravi <prasad.s.ravi@HOUSEHOLD.COM>
Subject:      Re: set variable name when import excel file
Comments: To: "Huang, Ya" <yhuang@AMYLIN.COM>
Content-type: text/plain; charset=us-ascii

Ya:

     It is in Online doc, look under IMPORT Procedure.


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.


   range-name       is the name that is assigned to a range address within a spreadsheet.



   absolute-rang    identifies the top left cell that begins the range and bottom right cell that ends the range. For
   e                Excel 4.0, 5.0, and 7.0 (95), the beginning and ending cells are separated by two periods; that is,
                    C9..F12 specifies a cell range that begins at cell C9, ends at cell F12, and includes all the cells
                    in between. For Excel 97 and 2000, the beginning and ending cells are separated by a colon, that
                    is, C9:F12.




      If you do not specify RANGE=, PROC IMPORT reads the entire
      spreadsheet. You may replace the equal sign with a blank.



Prasad Ravi






                         "Huang, Ya"
                         <yhuang@AMYLIN.COM>    To:    SAS-L@LISTSERV.UGA.EDU
                         Sent by: "SAS(r)       cc:
                         Discussion"            Subject: Re: set variable name when import excel file
                         <SAS-L@LISTSERV.UGA.E
                         DU>


                         07/29/2003 09:25 AM
                         Please respond to
                         "Huang, Ya"






Another undocumented feature? It seems to be a good one,
but I can't find it from Online Doc. Where do you find it?
Or is there a full list of options out there?

Thanks

Ya

-----Original Message-----
From: Robin High [mailto:robinh@UNLSERVE.UNL.EDU]
Sent: Tuesday, July 29, 2003 9:16 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: set variable name when import excel file


On Fri, 25 Jul 2003, Yang, Kai wrote:

> Normally, since SAS import an EXCEL data sheet, the variable name should
be
> the first row of the sheet. My question is how to write SAS statement
when I
> want to use the second row or specify row to be variable name?

Kai,

If the variable names are in the second row and the data follow in the
rows immediately below it, you can use the RANGE statement along with
GETNAMES=yes; for example if the data names are in row 2 and the data
begins in row 3, columns A through G, ending in row 80 then use,

PROC IMPORT DATAFILE="c:\<path>\sequence.xls"
            OUT=seq DBMS=excel2000 REPLACE;
  SHEET="new_data";
  GETNAMES=yes;
  RANGE="A2:G80";
run;

Similarly, if the variables names are in row 5 with the data immediately
following in row 6, columns A through G, ending in row 88, then specify
the range with:

RANGE="A5:G88";

Be sure to include the complete range the data are placed.


Robin High
University of Oregon