Programming the statistical procedures from SAS

Importing specific Excel .xlsx rows

Reply
Occasional Contributor
Posts: 15

Importing specific Excel .xlsx rows

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

Super Contributor
Posts: 339

Re: Importing specific Excel .xlsx rows

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;

Valued Guide
Posts: 3,208

Re: Importing specific Excel .xlsx rows

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

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 2 replies
  • 483 views
  • 0 likes
  • 3 in conversation