topic Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no in SAS Programming

proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

xxformat_com — Wed, 01 Apr 2020 17:36:07 GMT

With proc import dbms=xlsx, it is possible to use getnames=no.

But with the xlsx engine, I get an error message when using getnames=no.

Is it meant to be or is it a bug?

libname demo excel "&xxdemo./reporting/class.xlsx";

data demo.'All Students'n;  
    set sashelp.class;
run;

libname demo clear;

proc import datafile = "&xxdemo./reporting/class.xlsx" 
            out      = demo
            dbms     = xlsx
            replace;
    getnames=no;
    range='All_Students$A2:0';
run;


libname demo xlsx "&xxdemo./reporting/class.xlsx";* getnames=no;

data demo;  
    set demo.'All_Students$A2:0'n; 
run;

libname demo clear;

proc print data=demo;
run;

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

ChrisNZ — Wed, 01 Apr 2020 20:58:45 GMT

It is meant to be this way.

getnames is a statement that only proc import supports.

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

xxformat_com — Wed, 01 Apr 2020 21:39:42 GMT

Do you have any reference for this say or is it just a personal opinion?

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

ChrisNZ — Wed, 01 Apr 2020 22:21:07 GMT

I've been using SAS for 30 years. Also a negative cannot be proven. Also, google is your friend if you need to investigate. 🙂

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

ballardw — Wed, 01 Apr 2020 22:35:21 GMT

Actually GETNAMES is also available with Proc Access for certain file types (Lotus 1-2-3 Wkn anyone). Other than that Proc Import is the only place you'll find this commonly. There may be other procs but they are very likely to be somewhat esoteric and not available to everyone.

From the on-line documentation for

Import and Export Microsoft Excel Files Using the LIBNAME Statement

The LIBNAME statement method for importing and exporting Microsoft Excel workbook files generates SAS LIBNAME statement code. The LIBNAME statement uses the Microsoft ACE driver or Microsoft Jet driver to access data in Microsoft Excel workbook files.

Statement Options to Import or Export Excel Data
Data Source	Syntax	Valid Value	Default Value	PROC IMPORT	PROC EXPORT
EXCEL	DBDSOPTS	Excel data set options		Yes	Yes
	DBSASLABEL	Compat \| None	Compat	Yes	No
	GETNAMES	Yes \| No	Yes	Yes	No
	MIXED	Yes \| No	No	Yes	No
	NEWFILE	Yes \| No	No	No	Yes
	RANGE	range name		Yes	No
	SCANTEXT	Yes \| No	Yes	Yes	No
	SCANTIME	Yes \| No	Yes	Yes	No
	SHEET	sheet name		Yes	Yes
	TEXTSIZE	1 to 32767	1024	Yes	No
	USEDATE	Yes \| No	Yes	Yes	No

There are lots of issues when dealing with Excel XLS or XLSX files. One is that there have been several file formats since 1997. So the same approach can't work for all the files. There is a very big difference in XLSX (essentially Zipped XML) and the binary XLS. Other things that often have an impact: the bitness of your SAS and Office products (if you have a mix of 64bit for one and 32 for the other then some options go away), another is if you have SAS/Access to Pc Files licensed.

And that's even before we get to the specific content of any given file.

If you are going to read many files then Proc Import is not the way to go because you will run into inconsistencies of variable type, length and name as Proc Import makes a guess for each and every separate file imported.

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

xxformat_com — Wed, 01 Apr 2020 22:38:48 GMT

Hi, Here I'm not trying to compare xls vs xlsx or older Excel version with more recent version.
I'm only looking at xlsx engine vs. dbms=xlsx for the most recent Excel version.

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

Tom — Wed, 01 Apr 2020 23:05:43 GMT

There is no mention of any GETNAMES option on the reference page for the XLSX libname engine.

https://documentation.sas.com/?docsetId=acpcref&docsetTarget=n0oj9f6i838mymn148890ckla700.htm&docsetVersion=9.4&locale=en

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

xxformat_com — Wed, 01 Apr 2020 23:08:37 GMT

Thanks. I missed that point.

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

ChrisNZ — Wed, 01 Apr 2020 23:09:20 GMT

@ballardw I stand corrected. Old skeletons in the closet. 🙂

Re: proc import dbms=xlsx vs. xlsx engine: difference when using getnames=no

ballardw — Thu, 02 Apr 2020 01:16:36 GMT

@ChrisNZ wrote:

@ballardw I stand corrected. Old skeletons in the closet. 🙂

I only found that because of a pretty wide search in the documentation. I don't think I have ever actually touched Proc Access code. Basically I was expecting all the references to come back for Proc Import.