topic PROC IMPORT to get a specific range in SAS Procedures

PROC IMPORT to get a specific range

GreggB — Thu, 29 Sep 2016 14:36:45 GMT

Here's my step:

proc import datafile="G:\Departments\Research\test scores\SCREADY\&prevyr.-&yr.\&yr.SCREADY.xlsx" out=state dbms=xlsx replace;
sheet="&yr SC READY STATE";
range="A1:BS97";
run;

I'm also getting columns BT through DL, which I don't want.

Re: PROC IMPORT to get a specific range

Shmuel — Thu, 29 Sep 2016 17:58:16 GMT

I'm not sure but you may try:

libname mydata xlsx "G:\Departments\Research\test scores\SCREADY\&prevyr.-&yr.\&yr.SCREADY.xlsx";

proc import datafile=mydata;
sheet="&yr SC READY STATE";
range="A1:BS97";
run;

Re: PROC IMPORT to get a specific range

Arpit_Agarwal — Thu, 29 Sep 2016 18:41:22 GMT

Try this -

proc import datafile="C:\TESTFILE.xlsx" out=testme dbms=excel replace;

getnames=yes;

range='A1:BS27'; /*I set to 27, as I just had to make a sample sheet to test what's not working for you*/

run;

Re: PROC IMPORT to get a specific range

ShajiVijayan — Fri, 15 Feb 2019 16:48:11 GMT

This is a very old thread.. But I wish to answer it incase anyone comes here looking for the answer:

the coding will be

proc import datafile = 'the path of the file'
out = name of the dataset you wish to have
dbms = xls or xlsx depending on the file extension
range = 'sheet name $ and the range';
run;

As per your data and file path the coding will be:

proc import datafile="G:\Departments\Research\test scores\SCREADY\&prevyr.-&yr.\&yr.SCREADY.xlsx"
out=state
dbms=xlsx replace;
range="&yr SC READY STATE$ A1:BS97";
run;

Hope this helps

Re: PROC IMPORT to get a specific range

deedee07 — Mon, 03 May 2021 03:11:14 GMT

Hi, what if we have different no of data on monthly basis? Is there any other ways to not hard coded the range while working with the same file but having different no of data?

Thanks

Re: PROC IMPORT to get a specific range

kpberger — Fri, 30 Jul 2021 17:44:22 GMT

Yes, you can set the range to

range="A3:0";

if you want to start reading in data from the third row and capture all rows and columns with data regardless of how many there are.