03-21-2017 03:32 PM - edited 03-21-2017 03:34 PM
I have a data table "list" which contains 2 variables: path (the full path of a fileincluding ame and extension; files can be in different subdirectories) and name (the name of the files, excluding extensions).
Then create a macro list for each of the variables:
proc sql noprint; select path ,the_name into :path separated by ',', :the_name separated by ',' from list; quit;
Then I use these 2 macro lists in a macro that will import these files:
%macro import_from_all_subdirectories(file_path,file_name); %let k=1; %let the_path= %scan(&file_path, &k); %let name= %scan(&file_name, &k); %do %while("&the_path" NE ""); proc import datafile='&the_path' dbms=xls out=&name; run; %let k = %eval(&k + 1); %let the_path = %scan(&the_path, &k); %let name = %scan(&name, &k); %end; %mend; %import_from_all_subdirectories(&path,&the_name);
But I get an error message that "More positional parameters found than defined". Please help me slove this issue.
03-21-2017 07:02 PM
You've overly complicated this a bit in several places. If you're trying to import the file, why not specify the entire path than try to compile it? And when concatenating, you forgot the \ in as a spacer.
Anyways, here's one way that works - tested it out.
%macro list_files(dir,ext); %local filrf rc did memcnt name i; %let rc=%sysfunc(filename(filrf,&dir)); %let did=%sysfunc(dopen(&filrf)); %if &did eq 0 %then %do; %put Directory &dir cannot be open or does not exist; %return; %end; %do i = 1 %to %sysfunc(dnum(&did)); %let name=%qsysfunc(dread(&did,&i)); %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do; %put &dir\&name; %let file_name = %qscan(&name,1,.); %put &file_name; data _tmp; length dir $512 name $100; dir=symget("dir"); name=symget("name"); path = catx('\',dir,name); the_name = substr(name,1,find(name,'.')-1); run; proc append base=list data=_tmp force; run; quit; proc sql; drop table _tmp; quit; %end; %else %if %qscan(&name,2,.) = %then %do; %list_files(&dir\&name,&ext) %end; %end; %let rc=%sysfunc(dclose(&did)); %let rc=%sysfunc(filename(filrf)); %mend list_files; %macro import_file(path, file_name, dataset_name ); proc import datafile="&path.\&file_name." dbms=xlsx out=&dataset_name replace; run; %mend; %list_files(c:\_localData\temp, xlsx); data _null_; set list; string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');'); call execute (string); run;
03-21-2017 03:42 PM
Are all your files the same structure?
If so, you don't need a macro.
Check your 'order of operations' inside your macro.
You're trying to use macro variables that don't exist when calling the macro and they'll only exist within the macro.
03-21-2017 03:49 PM
I use macro variables when I call the macro because these are the macro lists that I created (I assume I can use macro lists)?
As for the structure of the files, what do you mean by that? They are all Excel files, but from different folders.
03-21-2017 04:03 PM
File structure, a concept completely foreign to Excel, is that the variables in each column represent the same basic data a the same column in another file. Example: the first column in two different files is an ID value that is numeric, the second is the first name of a client and the third colum represents a last name. In the same order with similar characteristics, which for character values means that the maximum lengths of any value are at least similar, say 20 characters in one file and 24 in the other. Also the actual data starts on the same row in both files and if one has column headers the other should as well. The number of rows may differ as the main difference in file layout.
03-21-2017 04:09 PM
This call using your macro variables created a shown will fail in almost every case:
Reason: You define the macro with two parameters.
The values of your macro variables will have commas embedded because you use "separated by ',' "
So the second value in the &path list will be treated by the macro processor as the second parameter and everything else after will be unrecognized parameters and generate the error: More positional parameters found then defined.
Please see this example:
%let parm = a, b, c; %macro dummy (var); %mend; %dummy(&parm);
So you probably should use " separated by ' ' " in building the parameter list in Proc Sql.
Note that passing commas in macro variables is a common problem causer.
03-21-2017 04:38 PM
I did the appropriate change, but now I get another error:
Physical file does not exist. C:\Documents and Setting\HP_Administrator\&the_path.xls
So for some reason it put the &the_path as the name of the file!
03-21-2017 05:21 PM
The macro variable 'swallows' the period so you need 2 periods, one to denote the end of the macro variable and one for the extension.
C:\Documents and Setting\HP_Administrator\&the_path..xls
03-21-2017 05:52 PM
In my original data I changed the variable path so that it includes the path up to .xls, and then in the macro I did:
and several other modifications, but it didn't work, I still get the message that the physical file doesn't exist
03-21-2017 06:06 PM
Are you running on a server version of SAS and trying to read files on your local harddrive? The server likely does not have a way of looking at your hard drive.
03-21-2017 06:13 PM
The SAS is on my computer and so are all the files, and I can create a macro that imports all the files if they are in the same folder
03-21-2017 06:22 PM
macro variables do not resolve within single quotes.
Run your macro with option mprint symbolgen; to see the code generated an resolved.
03-21-2017 06:44 PM
Yes, your scan function - why are you doing this? - doesn't make sense.
%let the_path= %scan(&file_path, &k); %let name= %scan(&file_name, &k);
03-21-2017 06:50 PM
what I want to do here is with each iteration of k, extract the kth path from the list of paths and the kth name from the list of names.
Then in the kth proc import I imput the kth path to extract the file, and do out the kth name to give it that name in SAS
Need further help from the community? Please ask a new question.