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 wrote:
According to the documentation:
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 -
According to the documentation:
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?
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.
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;
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;
@SASKiwi wrote:
According to the documentation:
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 -
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.