DATA Step, Macro, Functions and more

Import and Append Multiple MS Excel Spreadsheets

Reply
Regular Contributor
Posts: 194

Import and Append Multiple MS Excel Spreadsheets

G'day,

I've many MS Excel spreadsheets (all different names of course) which I need to append into one large file.

They should have all the same column names (Do I need to visually check this for each file or is there a shortcut?)

How do I get all of these files form one folder on my computer into SAS and append them into one large dataset?

I only know how to use Proc Import using the Wizard.

Any tips you can give will be most appreciated!!!

Super User
Posts: 19,805

Re: Import and Append Multiple MS Excel Spreadsheets

LMGTFY

Regular Contributor
Posts: 194

Re: Import and Append Multiple MS Excel Spreadsheets

Thanks! I'm not understanding how to write the file path and am getting an error:

3868  %let filepath=S:\2013\SR\Excel Files\Sample;

3869

3870  filename files pipe "dir/b %bquote(&filepath.\*.xlsx)";

3871

3872  data _null_;

3873

3874  infile files truncover;

3875

3876  length filename $20.;

3877

3878  input;

3879

3880  filename=scan(_infile_,1);

3881

3882  call execute(

3883

3884  'proc import out='||filename|| 'datafile="%bquote(&filepath.)\'||strip(filename)||'.xlsx"

3884! dbms=xlsx replace;

3885

3886   run;'

3887

3888  );

3889

3890  run;

NOTE: The infile FILES is:

      Unnamed Pipe Access Device,

      PROCESS=dir/b S:\2013\SR\Excel Files\Sample\*.xlsx,

      RECFM=V,LRECL=256

Stderr output:

The system cannot find the path specified.

NOTE: 0 records were read from the infile FILES.

NOTE: DATA statement used (Total process time):

      real time           0.11 seconds

      cpu time            0.01 seconds

Super Contributor
Posts: 275

Re: Import and Append Multiple MS Excel Spreadsheets

You could read all excel files in the same fold to SAS with codes below, then use proc append or data set to append all files.

%let filepath=drive:excelfile_fold;

filename files pipe "dir/b %bquote(&filepath.\*.xlsx)";

data _null_;

infile files truncover;

length filename $20.;

input;

filename=scan(_infile_,1);

call execute(

'proc import out='||filename|| 'datafile="%bquote(&filepath.)\'||strip(filename)||'.xlsx" dbms=xlsx replace;

run;'

);

run;

Super User
Posts: 19,805

Re: Import and Append Multiple MS Excel Spreadsheets

The path command depends if you're on UNIX or Windows and if you have spaces in them that's an issue as well.

You can test it in your unix or dos environment first to see if it works and then port that code back to SAS.

Regular Contributor
Posts: 194

Re: Import and Append Multiple MS Excel Spreadsheets

Sorry, I don't know Unix or Dos..any other suggestions?

Super User
Posts: 19,805

Re: Import and Append Multiple MS Excel Spreadsheets

1. Learn some

2. Post if you're on Unix or Windows, so the answer conforms to your system. Otherwise I'll post what works on mine Smiley Happy

Regular Contributor
Posts: 194

Re: Import and Append Multiple MS Excel Spreadsheets

I'm using Windows 7. Thanks!

Super User
Posts: 19,805

Re: Import and Append Multiple MS Excel Spreadsheets

24820 - Creating a Directory Listing Using SAS for Windows

filename files pipe 'dir /b "S:\2013\SR\Excel Files\Sample\*.xlsx" ';

Make sure the quotes are all there!

Regular Contributor
Posts: 194

Re: Import and Append Multiple MS Excel Spreadsheets

Thanks! I added the quotes. Now I'm getting another error but don't know why?

4123  filename files pipe 'dir /b "S:\2013\SR\Excel
4123! Files\ISample\*.xlsx" ';
4124
4125  data _null_;
4126
4127  infile files truncover;
4128
4129  length filename $20.;
4130
4131  input;
4132
4133  filename=scan(_infile_,1);
4134
4135  call execute(
4136
4137  'proc import out='||filename|| 'S:\2013\SR1314\Excel
4137! Files\Sample\'||strip(filename)||'.xlsx" dbms=xlsx replace;
4138
4139   run;'
           -
           180
ERROR 180-322: Statement is not valid or it is used out of proper order.

4140
4141  );
4142
4143  run;

Super User
Posts: 19,805

Re: Import and Append Multiple MS Excel Spreadsheets

Try a longer but a simpler solution, that you can try and debug yourself Smiley Happy

Regular Contributor
Posts: 194

Re: Import and Append Multiple MS Excel Spreadsheets

Thank you!

Super Contributor
Posts: 275

Re: Import and Append Multiple MS Excel Spreadsheets

The code work for me.

2    %let filepath=c:\test;

3

4    filename files pipe "dir/b %bquote(&filepath.)\*.xlsx";

5

6    data _null_;

7

8    infile files truncover;

9

10   length filename $20.;

11

12   input;

13

14   filename=scan(_infile_,1);

15

16   call execute(

17

18   'proc import out='||filename||

18 ! 'datafile="%bquote(&filepath.)\'||strip(filename)||'.xlsx" dbms=xlsx

18 ! replace;

19

20   run;'

21

22   );

23

24   run;

NOTE: The infile FILES is:

      Unnamed Pipe Access Device,

      PROCESS=dir/b c:\test\*.xlsx,RECFM=V,

      LRECL=32767

NOTE: 2 records were read from the infile FILES.

      The minimum record length was 9.

      The maximum record length was 10.

NOTE: DATA statement used (Total process time):

      real time           0.10 seconds

      cpu time            0.04 seconds

NOTE: CALL EXECUTE generated line.

1   + proc import out=cars                datafile="c:\test\cars.xlsx"

dbms=xlsx replace;

1   +

run;

NOTE: The import data set has 428 observations and 15 variables.

NOTE: WORK.CARS data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.08 seconds

      cpu time            0.09 seconds

2   + proc import out=class               datafile="c:\test\class.xlsx"

dbms=xlsx replace;

2   +

run;

NOTE: The import data set has 19 observations and 5 variables.

NOTE: WORK.CLASS data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

Super User
Posts: 19,805

Re: Import and Append Multiple MS Excel Spreadsheets

It didn't work for me - Windows 7 - path had spaces in it.

Super Contributor
Posts: 275

Re: Import and Append Multiple MS Excel Spreadsheets

it could solved the problem of path with space.

filename files pipe "dir/b %bquote("&filepath.\*.xlsx")";

Ask a Question
Discussion stats
  • 15 replies
  • 3397 views
  • 7 likes
  • 4 in conversation