topic Re: Libname xls in SAS Programming

Libname xls for xlm: import the range

SASdevAnneMarie — Wed, 16 Sep 2020 13:49:31 GMT

Hello Experts,
Is it possible to access the range in xlsm file using Libname xls?

My code is working:

options validvarname=any;

libname xl XLSX "\\XXX\TousIsin.xlsm";

data isin;
set xl.'VALEURS$B2:50'n;
run;

BUT when I replace B2:50 by range NAME:

data isin;
set xl.'VALEURS$test'n;
run;

My code doesn'y work. 😞

Thank you for your help!

Re: Libname xls

Oligolas — Wed, 16 Sep 2020 12:13:39 GMT

Hi,

Seems like you've tested it.

AFAIK you'll need the libname excel

libname xlfile excel "your xlsm file" access=read;
proc print data=sashelp.vtable (keep=libname memname memtype);
where libname eq 'XLFILE';
run;

Re: Libname xls

SASdevAnneMarie — Wed, 16 Sep 2020 13:32:02 GMT

Thank you,
It doesn't work:
ERROR: Connect: Classe non enregistrée
ERROR: Error in the LIBNAME statement.

I would like to import the range from xlsm file.

Re: Libname xls

Kurt_Bremser — Wed, 16 Sep 2020 13:46:10 GMT

Did LIBNAME XLS (which you previously mentioned) actually work? This looks as if you do not have ACCESS to PC Files licensed or installed, or are running SAS on a non-Windows platform.

Re: Libname xls

SASdevAnneMarie — Wed, 16 Sep 2020 13:50:51 GMT

Hello Kurt,

This code is working:

options validvarname=any;

libname xl XLSX "\\XXX\TousIsin.xlsm";

data isin;
set xl.'VALEURS$B2:50'n;
run;

BUT when I replace B2:50 by range NAME:

data isin;
set xl.'VALEURS$test'n;
run;

My code doesn'y work. 😞

Re: Libname xls

Kurt_Bremser — Wed, 16 Sep 2020 14:09:07 GMT

Run this:

libname xl XLSX "\\XXX\TousIsin.xlsm";

proc sql;
select memname from dictionary.tables where libname = "XL";
quit;

and look at the dataset names you get in the library.

Re: Libname xls

SASdevAnneMarie — Wed, 16 Sep 2020 15:11:22 GMT

Thank you,
I have the datasets with page name (the data by page), not by range.

Re: Libname xls

SASdevAnneMarie — Wed, 16 Sep 2020 16:24:12 GMT

I mean that my ranges are named, I didn’t se tables by range.

Thank you

Re: Libname xls

Kurt_Bremser — Wed, 16 Sep 2020 17:01:24 GMT

A little research revealed that the XLSX engine does not support named ranges, so you have (AFAIK) three options:

use LIBNAME EXCEL. Works only on Windows, and SAS and Office (installed on the SAS host) must be of the same bitness (32/64)
LIBNAME XLS, with the same restrictions regarding bitness (AFAIK)
use the PC Files Server and the EXCELCS engine.

Re: Libname xls

Oligolas — Thu, 17 Sep 2020 09:21:46 GMT

Hi,

it looks like you haven't SAS/ACCESS licensed.

You could try with proc Import but I think it won't work neither since it listed in the SAS/ACCESS Interface to PC Files but I would give it a try.

PROC IMPORT OUT= WORK.want 
            DATAFILE= "C:\fileWithNamedRanges.xlsm" 
            DBMS=EXCEL REPLACE;
            RANGE="range1"; 
            GETNAMES=YES;
            MIXED=YES;
            SCANTEXT=YES;
            USEDATE=YES;
            SCANTIME=YES;
RUN;

If none of the proposals work, maybe you can use some vba code to export a named ranges to a new xlsx workbook and Import the data.

I wasn't aware that what you posted before actually works, so thanks for this 🙂

libname xl XLSX "\\XXX\TousIsin.xlsm";
data isin;
set xl.'VALEURS$B2:50'n;
run;

Re: Libname xls

Kurt_Bremser — Thu, 17 Sep 2020 12:21:41 GMT

I'll recap what we know so far:

from the path, your SAS runs on Windows
you have ACCESS to PC Files licensed, as LIBNAME XLSX works
LIBNAME EXCEL or XLS gets the "class not registered" error

You either have a the necessary ACE or JET engine not available, or a bitness problem. See this SAS Note for possible remedies: https://support.sas.com/kb/63/750.html