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!!!
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
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;
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.
Sorry, I don't know Unix or Dos..any other suggestions?
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
I'm using Windows 7. Thanks!
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!
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;
Thank you!
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
It didn't work for me - Windows 7 - path had spaces in it.
it could solved the problem of path with space.
filename files pipe "dir/b %bquote("&filepath.\*.xlsx")";
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.