BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

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

15 REPLIES 15
jcis7
Pyrite | Level 9

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

slchen
Lapis Lazuli | Level 10

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;

Reeza
Super User

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.

jcis7
Pyrite | Level 9

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

Reeza
Super User

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

jcis7
Pyrite | Level 9

I'm using Windows 7. Thanks!

Reeza
Super User

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!

jcis7
Pyrite | Level 9

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;

Reeza
Super User

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

jcis7
Pyrite | Level 9

Thank you!

slchen
Lapis Lazuli | Level 10

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

Reeza
Super User

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

slchen
Lapis Lazuli | Level 10

it could solved the problem of path with space.

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 8655 views
  • 7 likes
  • 4 in conversation