SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Importing Excel Files with namerow and range options

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 104
Accepted Solution

Importing Excel Files with namerow and range options

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


Accepted Solutions
Solution
‎05-08-2017 08:13 AM
Frequent Contributor
Posts: 103

Re: Importing Excel Files with namerow and range options

[ Edited ]


@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

________________________

- That still only counts as one -

View solution in original post


All Replies
Super User
Posts: 3,101

Re: Importing Excel Files with namerow and range options

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? 

Frequent Contributor
Posts: 104

Re: Importing Excel Files with namerow and range options

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.

Super User
Posts: 17,775

Re: Importing Excel Files with namerow and range options

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;
Super User
Posts: 9,671

Re: Importing Excel Files with namerow and range options

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;
Solution
‎05-08-2017 08:13 AM
Frequent Contributor
Posts: 103

Re: Importing Excel Files with namerow and range options

[ Edited ]


@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

________________________

- That still only counts as one -

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 7774 views
  • 2 likes
  • 5 in conversation