Range option not working on PROC IMPORT from xlsx in SAS EG

[scify]

I have a somewhat unusual excel file, received from a client. For some reason, the client decided to embed information in the first column of the sheet that isn't actually part of the data, and this information occupies more rows than the actual data does. I tried to restrict the incoming information with the RANGE= option, but that doesn't seem to have had any effect. I was able to get around the first column ending up in the data set using the KEEP= function, but it's still pulling in all of the (blank) rows between where the data range ends and the informational column ends.

proc import datafile="/clients/members.xlsx"
	out=mems (keep=PERSON_ENTPRS_ID FIRST_NM LAST_NM BIRTH_DT SRC_PRV_ID ORG_LOC_NM PRV_ATTR_MTHD_CD MBR_PRV_ATTR_EXP_DT)
	dbms=xlsx 
	replace;
	range='Members$B1:I39';
	sheet='Members';
quit;

I know I can delete the blank rows with a data step after the PROC IMPORT, but, as I understand it, I shouldn't have to if I'm using the range option. For reference, I've tried the range with and without the sheet name.

[rogerjdeangelis]

Reading a block of data from a larger excel table

inspied by
https://goo.gl/XC47v0
https://communities.sas.com/t5/SAS-Enterprise-Guide/Range-option-not-working-on-PROC-IMPORT-from-xlsx-in-SAS-EG/m-p/335030

HAVE excel table class
======================

Up to 40 obs from sashelp.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5
  5    Henry       M      14     63.5      102.5
  6    James       M      12     57.3       83.0
  7    Jane        F      12     59.8       84.5
  8    Janet       F      15     62.5      112.5
  9    Jeffrey     M      13     62.5       84.0
 10    John        M      12     59.0       99.5


WANT  ( SAS datset WANT)

Up to 40 obs WORK.WANT total obs=3

Obs    SEX    AGE    HEIGHT

 1      F      14     62.8
 2      M      14     63.5
 3      M      12     57.3

DETAILS

Want the block below

Up to 40 obs from sashelp.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
               +----------------------+
  4    Carol   |   F      14     62.8 |    102.5
  5    Henry   |   M      14     63.5 |    102.5
  6    James   |   M      12     57.3 |     83.0
               +----------------------+
  7    Jane        F      12     59.8       84.5
  8    Janet       F      15     62.5      112.5
  9    Jeffrey     M      13     62.5       84.0
 10    John        M      12     59.0       99.5


WORKING CODE

     xel.'class$B1:D2'n(obs=0)  (get the names)
     union
      xel.'class$B4:D7'n        (get the block)

FULL SOLUTION
=============

* create sheet for input;
%let fyl=d:/xls/class.xlsx;
%utlfkil(&fyl);
libname xel "&fyl";
data xel.class;
 set sashelp.class;
run;quit;
libname xel clear;

* extract block;
proc sql;
  create
     table want as
  select
     *
  from
    xel.'class$B1:D2'n(obs=0)
  union
    all
  select
     *
  from
    xel.'class$B4:D7'n
;quit;