how to get .xlsx worksheet names added as a variable using append

[hixsone]

I have a single xlxs file with multiple worksheets.  The same 3 variables are on each worksheet, but the worksheet name provides the group id the data belongs to.

I want to import the worksheets; append into a single datafile; and analyze.  ODBC connect to open and PROC APPEND do this easily.  My problem is there is there doesn't appear to be the option to add worksheet name as an additional output var in the appended data output table to provide a group id for analysis.

What I get from ODBC + PROC APPEND

var1 var2 var3

What I want

worksheet_name  var1 var2 var3

Know could just do create query for each worksheet and add the name variable there, then do append; but my actual file has 23 worksheets and trying to simplify.  Any ideas or direction to something I am missing are appreciated!  Thanks, Eric

[Reeza]

Can you use a libname method to import your data? If so then the INDSNAME would be an option to add the sheet name easily.

libname inxls xlsx 'Path to xlsx file';

data want;

set sheet1 sheet2 sheet3 indsname=source;

sheet=source;

run;

libname inxls;

[hixsone]

Thank you for the reply!

The libname statement works but am erroring because looking for Sheet1...3 in Work library for some reason.  Here is log:

25 libname inxls xlsx 'C:\Users\hixsone\Documents\Temp\mort3.xlsx';
NOTE: Libref INXLS was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\Users\hixsone\Documents\Temp\mort3.xlsx
26 data want2;
27 set Sheet1 Sheet2 Sheet3 indsname=source;
ERROR: File WORK.SHEET1.DATA does not exist.
ERROR: File WORK.SHEET2.DATA does not exist.
ERROR: File WORK.SHEET3.DATA does not exist.
28 sheet=source;
29 run;

[Tom · Accepted solution]

Just use the XLSX libname engine and the INDSNAME= option on the SET statement.

libname mydata xlsx 'name of file.xlsx';
proc sql noprint ;
  select catx('.',libname,memname)
    into :dslist separated by ' '
    from dictionary.members 
    where libname='MYDATA'
  ;
quit;

data want ;
   length dsn group $50 ;
   set &dslist indsname=dsn ;
   group = scan(dsn,-1,'.') ;
run;

Not sure why you would want to bother with ODBC.  Perhaps if could help if the sheets are not in a normal data format.

[hixsone]

Thank you for the reply!  This worked with a couple modifications.  Had to remove and characters (e.g. '&', ',') and spaces from worksheet names.  Also had to change 'length dsn group $50' to '$250' because some of the worksheet names were long.

Regarding why ODBC: Originaly I just used toolbar controls and drag/drop table append (i.e. open ODBC source, machine source, select file, select worksheets).  Avoiding writing code...