BookmarkSubscribeRSS Feed
scify
Obsidian | Level 7

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.

 

1 REPLY 1
rogerjdeangelis
Barite | Level 11
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;

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1619 views
  • 0 likes
  • 2 in conversation