Using the macro language this way is a complex topic, but it looks to me like the macros you posted all work. You want the first version of %import_file, not the one you changed to have an asterisk in it.
The job of %list_files() is to make an output dataset, work.list, which has the name and path of each Excel file found. If I run:
%list_files(Q:\junk\Excel, xlsx)
proc print data=list ;
run ;
It works, and the PROC PRINT of work.list shows:
Obs dir name path the_name
1 Q:\junk\Excel\MoreExcel myotherxl.xlsx Q:\junk\Excel\MoreExcel\myotherxl.xlsx myotherxl
2 Q:\junk\Excel myxl.xlsx Q:\junk\Excel\myxl.xlsx myxl
So it succeeded in finding two Excel files, and you can see the directory and name of each.
The job of %import_file is to import one excel file and write one SAS dataset. So if I want to convert each of these excel files into SAS datasets I could code it as:
%import_file(Q:\junk\Excel,myxl.xlsx,want1)
%import_file(Q:\junk\Excel\MoreExcel,myotherxl.xlsx,want2)
And that would work. It would create work.want1 and work.want2. But the challenge with that approach is I need to write one macro call per Excel file, and I need to know the name and path for each file.
The job of the data _null_ step with the CALL EXECUTE is to read the data in WORK.LIST (which has one row per Excel file), and then generate one call to %import_file for each Excel file. So instead of hard-coding two calls to %import_file(), I can code:
data _null_;
set list;
string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');');
call execute (string);
run;
And CALL EXECUTE will generate two calls to %import_file, using the values of the variables in work.list to specify the directory, and the file name. The log from running that step is:
98 data _null_;
99 set list;
100 string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');');
101 call execute (string);
102 run;
MPRINT(IMPORT_FILE): proc import datafile="Q:\junk\Excel\MoreExcel\myotherxl.xlsx" dbms=xlsx out=test01 replace;
MPRINT(IMPORT_FILE): run;
MPRINT(IMPORT_FILE): proc import datafile="Q:\junk\Excel\myxl.xlsx" dbms=xlsx out=test02 replace;
MPRINT(IMPORT_FILE): run;
NOTE: There were 2 observations read from the data set WORK.LIST.
NOTE: CALL EXECUTE generated line.
1 + proc import datafile="Q:\junk\Excel\MoreExcel\myotherxl.xlsx" dbms=xlsx out=test01 replace;
1 + run;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 19 observations and 6 variables.
NOTE: WORK.TEST01 data set was successfully created.
1 + ;
2 + proc import datafile="Q:\junk\Excel\myxl.xlsx" dbms=xlsx out=test02 replace;
2 + run;
NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with
options MSGLEVEL=I.
NOTE: The import data set has 19 observations and 6 variables.
NOTE: WORK.TEST02 data set was successfully created.
2 +
The benefit of this dynamic programming approach is that you don't need to know the names of the Excel files, or how many there are. In your case, if you have twelve xlsx files, you should be able to run:
options mprint ;
proc delete data=list ;
run ;
%list_files(C:\Deskop\Budget, xlsx)
data _null_;
set list;
string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');');
call execute (string);
run;
And it should output twelve datasets, work.test01-work.test12.
If that is not working, please run that code and then post the full log.
... View more