topic Re: not to generate variable names from excel in SAS Programming

not to generate variable names from excel

NickR — Thu, 03 Feb 2011 21:14:07 GMT

Hello,

I'm using SAS 9.1.3 and I need to import excel 2007 (.xlsx) files into SAS so, I am using SAS/ACCESS interface to ODBC with the Excel 2007 ODBC driver. This method works fine, but it automatically generates variable names from the first row of excel sheet. Is there an option to prevent this (like GETNAMES=NO in proc import)?

My code:

libname myxls odbc required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
dbq=c:\spreadsheet.xlsx"
access=readonly defer=yes;

data one;
set myxls."data$A9:Z500"n;
run;

thanks for your time!! Message was edited by: Nick R

Re: not to generate variable names from excel

ariari — Sun, 06 Feb 2011 07:25:35 GMT

http://www.ats.ucla.edu/stat/sas/faq/rwxls8.htm

Re: not to generate variable names from excel

sbb — Sun, 06 Feb 2011 20:11:02 GMT

More the topic, use this Google advanced search argument to find relevant SAS support http://support.sas.com/ topic papers/references.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

sas 9.1.3 excel xlsx support site:sas.com

sas 9.1.3 getnames site:sas.com

Re: not to generate variable names from excel

NickR — Mon, 07 Feb 2011 15:23:35 GMT

The link you've provided describes importing .XLS files. I need to import .XLSX (excel 2007) files, without generating variable names from the spreadsheet.

Re: not to generate variable names from excel

NickR — Mon, 07 Feb 2011 15:36:03 GMT

Thanks. I tried searching using Google advanced search argument but no luck so far. Will contact SAS technical support.

Re: not to generate variable names from excel

Ksharp — Wed, 23 Feb 2011 07:15:11 GMT

How about :
[pre]
libname xls excel path='c:\test.xls' header=no;
proc sql;
create table xls as
select * from xls.'Sheet1$'n;
quit;
[/pre]

Ksharp