topic Re: XLSX libname PROC COPY specify a range to apply to all sheets in SAS Programming

XLSX libname PROC COPY specify a range to apply to all sheets

data_null__ — Fri, 21 Aug 2020 18:58:18 GMT

My variable names are on row 2. I would like to PROC COPY all sheets with range $A2:0 but I don't know if it is possible or if I just can't figure the syntax.

30         libname xl xlsx './zzzzzzzzzzz.xlsx' access=read;
NOTE: Libref XL was successfully assigned as follows: 
      Engine:        XLSX 
      Physical Name: (system-specific file/path name)
31         
32         data test;
33            set xl.'ADJAE007$A2:0'n;
34            run;

NOTE: The import data set has 122 observations and 13 variables.
NOTE: The data set WORK.TEST has 122 observations and 13 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.02 seconds
37         
38         proc copy in=xl out=work;
39            select 'ADJAE007$A2:0'n;
40            run;

ERROR: The file XL.ADJAE007$A2:0 (memtype=ALL) was not found, but appears on a SELECT statement.
NOTE: Statements not processed because of errors noted above.

Re: XLSX libname PROC COPY specify a range to apply to all sheets

Ksharp — Sat, 22 Aug 2020 12:19:08 GMT

John,

Could you try firstobs option ?

options firstobs=2;
libname x v9 'c:\temp\';
proc copy in=work out=x;
select have;
run;

Re: XLSX libname PROC COPY specify a range to apply to all sheets

Tom — Sun, 23 Aug 2020 14:24:16 GMT

The XLSX libname requires that the sheets be constructed like a dataset. But you can use it to help you get the list of sheet names to use in generating code to read starting from the second row.

So make a libname pointing to the XLSX file and get the list of members, for example by using PROC CONTENTS. Then use that list of members to generate PROC IMPORT code as below or the data step code in your example.

While you are at it you could add some logic to deal with sheet names that are not valid dataset names.

%let path=C:\downloads\;
%let fname=Myfile.xlsx;
libname OUT "&path.sas";
libname IN xlsx "&path.&fname";

* Set VALIDMEMNAME option to EXTEND to handle sheetnames with spaces ;
* Set VALIDVARNAME option to V7 ;
options validmemname=extend validvarname=v7;

* Get contents information from source workbook ;
proc contents data=IN._all_ noprint out=sheets(keep=memname);
run;
libname IN clear;
* Reset VALIDMEMNAME option to COMPAT ;
options validmemname=compat;

* Keep one observations per sheet ;
* Generate RANGE from sheet name;
* Generate valid MEMNAME from the SHEET names ;
* Generate PROC IMPORT code to read from second row and write to OUT library ;
data sheets;
  set sheets ;
  if memname ne lag(memname);
  length range $50 ;
  range=quote(cats(memname,'$A2:'));
  memname=translate(trim(prxchange('s/([^a-zA-Z0-9]+)/ /',-1,memname)),' _','_ ');
  memname=prxchange('s/(^[0-9])/_$1/',1,memname);
  call execute(catx(' ','proc import dbms=xlsx datafile="&path\&fname" replace'
                       ,cats('out=out.',memname),';','range=',range,';run;'));
run;

proc print data=sheets;
run;