IT WORKED!!!
Its just that instead of naming the files test1 test2 ... I wanted to name them by their real names. But the thingis that for the "fourth file" I was getting 2 empty data sets, namely "fourth" and "file" - I guess that this is because of the empty space, so in case when there are empty spaces I just replaced them with underscores. I also added a variable in each file that will contan the file's name like this I can know which data came from which file. Here are the small modifications that I did:
%macro import_file(path, file_name, dataset_name );
proc import
datafile="&path.\&file_name."
dbms=xls
out=&dataset_name replace;
run;
data &dataset_name;
set &dataset_name indsname=source;
dsname = scan(source,2,'.');
run;
proc append base=master data=&dataset_name force;
run;
proc sql;
drop table &dataset_name;
quit;
%mend;
data list;
set list;
file_name = translate(trim(the_name),'_',' ');
run;
data _null_;
set list;
string = catt('%import_file(', dir, ', ', name,', ', file_name, ');');
/*string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');');*/
call execute (string);
run;
Thanks Reeza for the help!
If you like, please put all your steps into one post (including the appending step) so that I can mark it as the solution!!!
... View more
