Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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


Accepted Solutions
Solution
‎01-07-2017 12:37 PM
Super User
Super User
Posts: 6,364

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

[ Edited ]

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.

View solution in original post


All Replies
Grand Advisor
Posts: 17,389

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

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;

New Contributor
Posts: 4

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

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;

Solution
‎01-07-2017 12:37 PM
Super User
Super User
Posts: 6,364

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

[ Edited ]

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.

New Contributor
Posts: 4

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

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...

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 202 views
  • 0 likes
  • 3 in conversation