topic Re: How to specify a range for an Excel file with proc import where you only know the starting cell? in SAS Programming

How to specify a range for an Excel file with proc import where you only know the starting cell?

Ani7 — Tue, 07 Jul 2020 19:32:55 GMT

I'm trying to import a series of files in a macro where the only constant is that the data starts at cell B9 of Sheet1. What is the syntax for only specifying one part of the range?

proc import datafile = "path/filename.xlsx"
			out = want
			dbms = xlsx replace;
			range = "Sheet1$B9:";
run;

I have tried the above but that returns an error.

Re: How to specify a range for an Excel file with proc import where you only know the starting cell?

Reeza — Mon, 05 Sep 2022 19:31:27 GMT

Try using 0 as the end where it will default to the full range available.

proc import datafile = "path/filename.xlsx"
			out = want
			dbms = xlsx replace;
			range = "Sheet1$B9:0";
run;

@Ani7 wrote:

I'm trying to import a series of files in a macro where the only constant is that the data starts at cell B9 of Sheet1. What is the syntax for only specifying one part of the range?
proc import datafile = "path/filename.xlsx"
			out = want
			dbms = xlsx replace;
			range = "Sheet1$B9:";
run;
I have tried the above but that returns an error.

Moderator note: See also this alternative from @PGStats:

Alternatively, you can use a LIBNAME statement, such as

libname xl xlsx " ..... /b9test.xlsx";

proc sql;
create table want1 as
select name, id
from xl.'Sheet1$B9:'n;
quit;

/* Or */
data want2;
set xl.'Sheet1$B9:'n;
run;

/* Or, read directly into any proc */
proc print data=xl.'Sheet1$B9:'n noobs; 
run;

It all works for me on SAS release: 9.04.01M6P110718

Re: How to specify a range for an Excel file with proc import where you only know the starting cell?

Ani7 — Tue, 07 Jul 2020 19:36:48 GMT

Unfortunately, that doesn't work and returns the following error:

ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
 
The SAS task name is [IMPORT (]
Segmentation Violation
 
Traceback of the Exception:
 
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sas(+0x15aa6e) [0x564e723c8a6e]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sas(+0x4cb0b) [0x564e722bab0b]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x144) [0x2af039c323c4]
/lib64/libpthread.so.0(+0xf630) [0x2af038aeb630]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimxlx(+0x187a9) [0x2af091f587a9]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimxlx(+0x14f83) [0x2af091f54f83]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimxlx(dbmscopy_read_header+0x94) [0x2af091f4d6c4]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimxlx(getvars+0xa) [0x2af091f4890a]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimctr(cpfdata+0x7c) [0x2af091d28ffc]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimctr(import+0x609) [0x2af091d28a79]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimctr(parsexe+0x24b) [0x2af091d27adb]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sasimpor(sasimpor+0x1a8) [0x2af091b10d28]
/gpfs/grid/sas/sashome/SASFoundation/9.4/sasexe/sas(vvtentr+0x13d) [0x564e722ba6ad]
/lib64/libpthread.so.0(+0x7ea5) [0x2af038ae3ea5]
/lib64/libc.so.6(clone+0x6d) [0x2af0395338cd]

Re: How to specify a range for an Excel file with proc import where you only know the starting cell?

Reeza — Tue, 07 Jul 2020 19:53:26 GMT

You can use RANGE= to specify the row number where PROC IMPORT starts to read data. Set the end point to “0”, and the code then determines the last row and last column. Specify RANGE="Sheetname$A#:0"; where # is the first data row. Thus, RANGE="sheet1$A3:0"; starts to read the data at row 3. If you use RANGE= for this purpose, do not specify the DATAROW= statement.

https://go.documentation.sas.com/?docsetId=acpcref&docsetTarget=n0msy4hy1so0ren1acm90iijxn8j.htm&docsetVersion=9.4&locale=en#p09degjribu2smn1eaqt5eq9on58

I would suggest restarting and trying it again. Not sure that issue is related to your import and not something else, otherwise show your code.

Re: How to specify a range for an Excel file with proc import where you only know the starting cell?

mklangley — Tue, 07 Jul 2020 19:55:33 GMT

What error are you getting? The code you included works for me.

Example:

test.xlsx:

Code:

proc import datafile = "path/test.xlsx"
			out = want
			dbms = xlsx replace;
			range = "Sheet1$B9:";
run;

Result:

Re: How to specify a range for an Excel file with proc import where you only know the starting cell?

PGStats — Tue, 07 Jul 2020 21:21:19 GMT

Alternatively, you can use a LIBNAME statement, such as

libname xl xlsx " ..... /b9test.xlsx";

proc sql;
create table want1 as
select name, id
from xl.'Sheet1$B9:'n;
quit;

/* Or */
data want2;
set xl.'Sheet1$B9:'n;
run;

/* Or, read directly into any proc */
proc print data=xl.'Sheet1$B9:'n noobs; 
run;

It all works for me on SAS release: 9.04.01M6P110718

Re: How to specify a range for an Excel file with proc import where you only know the starting cell?

Reeza — Tue, 07 Jul 2020 21:33:18 GMT

It will depend on the structure of your data.