BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ani7
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

6 REPLIES 6
Reeza
Super User

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

Ani7
Obsidian | Level 7

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]
Reeza
Super User
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&doc...

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.
mklangley
Lapis Lazuli | Level 10

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

 

Example:

test.xlsx: 

mklangley_0-1594151574363.png

 

Code:

 

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

 

Result:

mklangley_1-1594151603378.png

 

PGStats
Opal | Level 21

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

 

PG

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 27333 views
  • 4 likes
  • 4 in conversation