Importing specific Excel .xlsx rows

copony — Sun, 27 Jul 2014 19:57:39 GMT

I'm relatively new to the DATA and PROC IMPORT steps of SAS (am more statistics oriented and have not managed importing data into SAS, specifically) and I'm wondering if there is a way to import specific rows into SAS. The problem with my Excel sheets is that they contain a lot of data in rows that I don't need. Also, the first line of the Excel sheet is not a list of variable names (I didn't design these spreadsheets, obviously!).

Specifically, this is what I need to do:

Import cell B2 (this is study ID#) and then import A10, B10 and A11, B11. Then I would like B12 - Y12 (these are time variables) and then B24 - Y24 (the values that correspond to B12-Y12 variables).

Anything in an A cell is a variable, but I don't necessarily NEED it as I could figure out what variable I'm looking at later on.

There are multiple worksheets in each workbook, and I am willing to read each workbook/worksheet in by hand if necessary.

Ordinarily I would run a macro/VBA in Excel to get this info but our security policies are such that we aren't allowed to run macros in Excel or Access!

All suggestions appreciated!9

Re: Importing specific Excel .xlsx rows

user24feb — Mon, 28 Jul 2014 06:30:12 GMT

Something like:

Proc Import Datafile = "C:\Path\XL_FILE.xlsx" /* change path and name */

Out = SAS_XL_File

DBMS = Excel2007 /* .. if it is XL 2007 .. */

Replace;

Getnames=Yes; /* .. the first line will become the variable names */

Range="Sheet1$B12:Y12"; /* don't forget the $ between sheet name and the Excel cells .. */

Run;

Re: Importing specific Excel .xlsx rows

jakarman — Mon, 28 Jul 2014 06:37:19 GMT

This is an duplicate question there is also an answer from pgstats

topic Re: Importing specific Excel .xlsx rows in Statistical Procedures

Importing specific Excel .xlsx rows

Re: Importing specific Excel .xlsx rows

Re: Importing specific Excel .xlsx rows