topic Re: Import and Append Multiple MS Excel Spreadsheets in SAS Programming

Import and Append Multiple MS Excel Spreadsheets

jcis7 — Thu, 24 Apr 2014 22:22:25 GMT

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

Re: Import and Append Multiple MS Excel Spreadsheets

Reeza — Thu, 24 Apr 2014 22:36:39 GMT

LMGTFY

Re: Import and Append Multiple MS Excel Spreadsheets

slchen — Fri, 25 Apr 2014 01:47:43 GMT

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;

Re: Import and Append Multiple MS Excel Spreadsheets

jcis7 — Thu, 01 May 2014 21:41:58 GMT

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

Re: Import and Append Multiple MS Excel Spreadsheets

Reeza — Thu, 01 May 2014 22:09:12 GMT

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.

Re: Import and Append Multiple MS Excel Spreadsheets

AndrewHowell — Fri, 02 May 2014 02:40:24 GMT

An alternative to the "pipe" statement (which spawns an O/S shell, something not always allowed) is to use the SAS file & directory functions - dopen(), fopen(), etc - to get the list of Excel spreadsheets in a directory, each of which can then be used in a subsequent import/libname statement. (This also removes the O/S dependency.)

Re: Import and Append Multiple MS Excel Spreadsheets

jcis7 — Fri, 02 May 2014 15:09:41 GMT

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

Re: Import and Append Multiple MS Excel Spreadsheets

Reeza — Fri, 02 May 2014 15:19:10 GMT

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

Re: Import and Append Multiple MS Excel Spreadsheets

jcis7 — Fri, 02 May 2014 15:21:42 GMT

I'm using Windows 7. Thanks!

Re: Import and Append Multiple MS Excel Spreadsheets

Reeza — Fri, 02 May 2014 16:11:42 GMT

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!

Re: Import and Append Multiple MS Excel Spreadsheets

slchen — Fri, 02 May 2014 16:20:35 GMT

The code work for me.

2 %let filepath=c:\test;

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

6 data _null_;

8 infile files truncover;

10 length filename $20.;

12 input;

14 filename=scan(_infile_,1);

16 call execute(

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

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

18 ! replace;

20 run;'

22 );

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

Re: Import and Append Multiple MS Excel Spreadsheets

Reeza — Fri, 02 May 2014 16:25:30 GMT

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

Re: Import and Append Multiple MS Excel Spreadsheets

jcis7 — Fri, 02 May 2014 16:29:45 GMT

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;

Re: Import and Append Multiple MS Excel Spreadsheets

Reeza — Fri, 02 May 2014 16:43:29 GMT

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

Re: Import and Append Multiple MS Excel Spreadsheets

slchen — Sat, 03 May 2014 01:58:20 GMT

it could solved the problem of path with space.

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

Re: Import and Append Multiple MS Excel Spreadsheets

jcis7 — Mon, 12 May 2014 16:12:50 GMT

Thank you!