read Excel table with varable names, but not on the first line

[WendyT]

I typically receive Excel files with some sort of explanatory material on the first few lines, and a data table below with variable names in the first row.

So far, I have been unable to come up with the right options (FIRSTOBS? DBDSOPTS?) and placement so that I can get just the data table, and I know I've got to be missing something obvious in the documentation.

Could someone please point me to some examples?

The following is a starting point for a table that starts with variable names on row 5, data on row 6.

I have SAS 9.2 and Interface to PCFF.

Thanks for any help you can give me!

Wendy T.


LIBNAME GETIT excel "&data_req\analysis\daily_q.xlsx" getnames=yes ;

DATA README1 ; SET GETIT.'daily q phase i$'n ;
DATA README2 ; SET GETIT.'daily q phase ii$'n ;

RUN ;

LIBNAME GETIT CLEAR ;
RUN ;

[GeoffNess]

The best resource I've come across for reading Excel files using the libname engine is http://www2.sas.com/proceedings/sugi31/024-31.pdf.

Is the data in the spreadsheets contained in named ranges? If not, can you get to them to define the ranges you want prior to reading them? This is the only way I'm aware of to get SAS to start reading data anywhere other than the top left cell on the specified sheet. For example, if you defined the name 'datarange' for the data you wanted on the sheet 'daily q phase i', then you could read this with

DATA README1 ; SET GETIT.datarange ;

HTH

[WendyT]

GeoffNess-

Thanks so much for the reference - I had not run across that particular one before.

I was hoping to avoid named ranges, but it looks like that may be the best solution if I don't want to clip the top of the files as I am doing now.

Wendy T.