topic Re: Importing Excel Files with namerow and range options in SAS Data Management

Importing Excel Files with namerow and range options

sarahsasuser — Thu, 28 Jan 2016 18:18:41 GMT

Hi All,

I need to import an excel sheet that has the field names in the second row and the data begins in the 3rd. Further, I need to import only a certain range. The following code gives me an error if I use the range:

PROC IMPORT OUT = NEW
           DATAFILE= "F:\data.xls"
           DBMS=XLS REPLACE;
           SHEET="Data";
            RANGE="A2:GF332";
           NAMEROW=2;
           DATAROW=3;
           GETNAMES=YES;
RUN;

If I use the .XLSX format file I can use the range option, but not the NAMEROW option. How do I get both?

Thanks,

Sarah

Re: Importing Excel Files with namerow and range options

SASKiwi — Thu, 28 Jan 2016 19:04:06 GMT

According to the documentation:

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0msy4hy1so0ren1acm90iijxn8j.htm

if you specify a range and GETNAMES=YES then the first row of the range is used to construct the column names and the second row is where the data starts. So NAMEROW and DATAROW become redundant in this case.

Does your sheet import correctly and do column names get assigned correctly using just RANGE and GETNAMES?

Re: Importing Excel Files with namerow and range options

sarahsasuser — Thu, 28 Jan 2016 19:07:38 GMT

You would think that by specifying the range (which would exclude the first row), using the range and getnames options would work,but it does not. When I comment out the datarow and namerow options, it pulls the first row in for the names.

Re: Importing Excel Files with namerow and range options

Reeza — Thu, 28 Jan 2016 20:10:20 GMT

Is this a one time thing?

If so, create a named range instead of a cell range and it works. Not sure why it doesn't work with a cell range.

/*Doesn't work*/
proc import out=want datafile='C:\_localdata\delete.xlsx' dbms=xlsx replace; range='A2:C6'; sheet='Sheet1';getnames=yes;
run;


/*Named range in Excel, Does work*/
proc import out=want datafile='C:\_localdata\delete.xlsx' dbms=xlsx replace; range='Input1';
run;

Re: Importing Excel Files with namerow and range options

Ksharp — Fri, 29 Jan 2016 01:47:28 GMT

Use RANGE= instead of

NAMEROW=2;
DATAROW=3;

proc import out=want datafile='/folders/myfolders/adae.xlsx' dbms=xlsx replace; 
range='Sheet1$A10:D20';
run;

Re: Importing Excel Files with namerow and range options

Oligolas — Mon, 08 May 2017 12:13:26 GMT

@SASKiwi wrote:

According to the documentation:

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

if you specify a range and GETNAMES=YES then the first row of the range is used to construct the column names and the second row is where the data starts. So NAMEROW and DATAROW become redundant in this case.

Alternatively,

you could specify a "named range" in Excel (open your workbook and hold CTRL+F3, then click new... select a region and enter a name)

/*import the whole sheet*/
PROC IMPORT datafile="C:\TEMP\workbook.xlsx" 
   OUT=want
   DBMS=XLSX REPLACE ;
   SHEET="...yourSheetName...";
   GETNAMES=YES;
RUN;

/*import a named range*/
PROC IMPORT datafile="C:\TEMP\workbook.xlsx" 
   OUT=want
   DBMS=XLSX REPLACE ;
   RANGE="...yourNamedRange...";
   GETNAMES=NO;
RUN;

Cheers