BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sarahsasuser
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Oligolas
Barite | Level 11


@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

________________________

- Cheers -

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

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
Quartz | Level 8

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
Super User

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
Super User

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
Barite | Level 11


@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

________________________

- Cheers -

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 37288 views
  • 3 likes
  • 5 in conversation