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.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.