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.
... View more