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.
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
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
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]
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:
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.