import multiple excel files then append them into one table

Reply
New Contributor
Posts: 2

import multiple excel files then append them into one table

I have 20 or so reports each stored in a different sheet in excel.

I have written some code to import them into SAS and now have 20 or so temporary SAS files.

I wish to append them all into one table in SAS.

What is the quickest way to do this?

Grand Advisor
Posts: 9,567

Re: import multiple excel files then append them into one table

Assuming those Sheets in Excel have the same data construction .

Code: Program

libname x xlsx '/folders/myfolders/x.xlsx';
data _null_;
set sashelp.vmember(where=(libname='X')) end=last;
if _n_ = 1 then call execute('proc sql;create table want as');
call execute('select * from x.'||nliteral(memname));
if not last then call execute('union all corr');
  else call execute(';quit;');
run;


Log: Program

Notes (9)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

53 

54 libname x xlsx '/folders/myfolders/x.xlsx';

NOTE: Libref X was successfully assigned as follows:

  Engine: XLSX

  Physical Name: /folders/myfolders/x.xlsx

55 data _null_;

56 set sashelp.vmember(where=(libname='X')) end=last;

57 if _n_ = 1 then call execute('proc sql;create table want as');

58 call execute('select * from x.'||nliteral(memname));

59 if not last then call execute('union all corr');

60 else call execute(';quit;');

61 run;

NOTE: There were 3 observations read from the data set SASHELP.VMEMBER.

  WHERE libname='X';

NOTE: DATA statement used (Total process time):

  real time 0.03 seconds

  cpu time 0.05 seconds

  

NOTE: CALL EXECUTE generated line.

1 + proc sql;

1 + create table want as

2 + select * from x."SHEET 3"N

3 + union all corr

4 + select * from x.SHEET1

5 + union all corr

6 + select * from x.SHEET2

7 + ;

NOTE: The import data set has 1 observations and 2 variables.

NOTE: The import data set has 1 observations and 2 variables.

NOTE: The import data set has 1 observations and 2 variables.

NOTE: Table WORK.WANT created, with 3 rows and 2 columns.

7 + quit;

NOTE: PROCEDURE SQL used (Total process time):

  real time 0.13 seconds

  cpu time 0.13 seconds

  

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: import multiple excel files then append them into one table

Alternatively learn VBA if you insist on using Excel at all.  Write a small macro in VBA, something like

Sub Xport ()

     MyFile = "c:\temp.csv";

     Open MyFile for output as #1

     foreach s in Workbook.Worksheets

          foreach c in s.Cells

               print #1, c.value

          next

     next

     Close #1

End Sub

Something like the above will write out all sheets to one CSV file.  You would need to restrict the c in Cells to only those you want to deal with, maybe you will need to quote things as well if they contain quotes etc.  I can only suggest without knowing your data, but the basic premise is to save all the data to CSV, then you can write a datastep import procedure to import that data, i.e. using a proper data transfer format.

Grand Advisor
Posts: 17,308

Re: import multiple excel files then append them into one table

If you've used a naming convention with a common prefix you can use the colon (Smiley Happy short cut:

This will append all the datasets together. Depending on your naming structure there may also be other options.

data want;

set prefix:;

run;

New Contributor
Posts: 2

Re: import multiple excel files then append them into one table

This would be good, however, I am getting this error

1315      data WANT;

1316      set WORK.RPT_:;

              ^

ERROR: Data set "WORK.RPT_" not found

ERROR: Expected a data set name or option name

1317      run;

all of the reports are named "RPT_...."

Grand Advisor
Posts: 17,308

Re: import multiple excel files then append them into one table

It was introduced in SAS 9.3 so your version may be too old Smiley Sad

Ask a Question
Discussion stats
  • 5 replies
  • 326 views
  • 6 likes
  • 4 in conversation