topic Re: libname excel - can we specify multiple ranges? in SAS Programming

libname excel - can we specify multiple ranges?

xxformat_com — Tue, 21 Apr 2020 09:25:48 GMT

Hi,

Let's work with an Excel file with 5 columns (A-E) where the first row contains the variable name.

We want to set together the first data row (line 2) and the five and sixth row (line 6-7) using the libname excel engine and a set statement in a dataset.

It won't be possible because the range has to include the variable name and a syntax like this 'SASHELP_CLASS$A1:E1;A6:E7'n would not work.

Question: is it just not possible with libname engine+set statement or am I using the wrong syntax to define multiple ranges.

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

data demo.'SASHELP CLASS'n;
    set sashelp.class;
run;

libname demo;

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

data test;  
    set demo.'SASHELP_CLASS$A1:E2'n
        demo.'SASHELP_CLASS$A1:E1;A6:E7'n;
run;

libname demo;

proc print data=test noobs;
run;

Re: libname excel - can we specify multiple ranges?

Kurt_Bremser — Tue, 21 Apr 2020 09:39:13 GMT

If you write a "dataset" with a blank in its name, you have to use that again when referring to it:

libname demo xlsx "/folders/myfolders/class.xlsx";

data demo.'SASHELP CLASS'n;
set sashelp.class;
run;

data test;  
set
  demo.'SASHELP CLASS$A1:E2'n
  demo.'SASHELP CLASS$A1:E1;A6:E7'n
;

run;

proc print data=test noobs;
run;

This code worked in my SAS UE.

Re: libname excel - can we specify multiple ranges?

xxformat_com — Tue, 21 Apr 2020 09:49:19 GMT

Thanks for the answer.

Actually, I was using the underscore because it was present in my Excel file because I was using a Excel engine, not a xlsx engine.

So basically the issue was not the syntax for the range

but the use of an Excel engine.

With excel engine, it does not work (not sure if it is a bug or just not implemented in the engine yet)

With xlsx engine, it works.

Re: libname excel - can we specify multiple ranges?

Kurt_Bremser — Tue, 21 Apr 2020 12:17:59 GMT

The EXCEL engine is Windows-only, and uses the ACE supplied by Microsoft; it is not available on UE (runs on Linux), therefore I used XLSX, which I recommend for future use anyway (you need to have files in the modern XLSX format, of course).

Re: libname excel - can we specify multiple ranges?

xxformat_com — Wed, 22 Apr 2020 10:17:54 GMT

Hi,

If the selection is working fine for distinct rows, it does seem to work for distinct columns.

'SASHELP CLASS$A:C'n; would select columns A, B and C which is ok.

'SASHELP CLASS$A:A;C:C'n; would also select columsn A, B and C instead of A and C.

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

data test;  
   set demo.'SASHELP CLASS$A:A;C:C'n;
run;

libname demo;