Importing Excel Files with namerow and range options

[sarahsasuser]

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

[SASKiwi]

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.

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

[sarahsasuser]

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.

[Reeza]

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;

[Ksharp]

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;

[Oligolas · Accepted solution]

---

---

@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