topic Re: proc import range=... starting from line 2 > dbms=xlsx vs. dbsm=excel De-Mystifying the SAS® LIB in SAS Programming

proc import range=... starting from line 2 > dbms=xlsx vs. dbsm=excel

xxformat_com — Wed, 01 Apr 2020 17:09:52 GMT

With the dbms=xlsx, it is possible to use range='All_Students$A2:0' to import all the lines from line 2. But it does not work with dbms=excel. Is it meant to be or is it just a bug in SAS 9.4?

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;

Re: proc import range=... starting from line 2 > dbms=xlsx vs. dbsm=excel De-Mystifying the SAS® LIB

ChrisNZ — Wed, 01 Apr 2020 21:08:52 GMT

It is meant to be this way.

You can point to the desired range when using the libname by using a name starting with $.

You may find these papers interesting De-Mystifying the SAS® LIBNAME Engine in Microsoft Excel: A Practical Guide and How to use SAS to read a range of cells from Excel

Re: proc import range=... starting from line 2 > dbms=xlsx vs. dbsm=excel De-Mystifying the SAS®

xxformat_com — Wed, 01 Apr 2020 21:49:51 GMT

I'm alraedy using a $ sign in front of the range.
The link you provided https://blogs.sas.com/content/sasdummy/2018/06/21/read-excel-range/ only use xlsx.
The question is on excel dbms/engine.

Do you have any example with excel?