Hi Everyone,
I wrote a Macro called "GET_PRODCUT_DATA". This Macro - among other parameters - needs an input filename. This filename, in turn, did I write into a macro variable, which I called file1. As I have more than one file, I created macro variables with the corresponding filename in the manner of
file1
file2
...
...
...
Now, I want to repetitivelyy call the Macro "GET_PRODCUT_DATA" within another Macro called "LOOP_CSV), so that I can loop through the different filenames which are stored in the macro var. However, when I run it (for example from 1 to 2, i.e. file1 and file2 filenames) it runs without Errors, but only the filename corresponding to &file1 is being used.
Why does it not loop throug &file2, &file3, etc.?
%MACRO LOOP_CSV;
%DO i = 1 %TO 2 /*&anz_files.*/;
%GET_PRODUCT_DATA(
p_sInOutProductFileCSV=&&file&i
,p_sInCSVDELIMITER = {
);
%end;
%MEND;
%LOOP_CSV;
Use options mprint symbolgen to reveral what happens; also use %put to help in diagnosing.
Your code as such looks OK, as this simple test shows:
%let file1=xxxx;
%let file2=yyyy;
%macro display_val(inval);
%put inval=&inval;
%mend;
%macro loop;
%do i = 1 %to 2;
%display_val(&&file&i);
%end;
%mend;
%loop
Personally, I prefer to run such repeats off a dataset:
data control;
input filename $;
cards;
xxxx
yyyy
;
run;
%macro display_val(inval);
%put inval=&inval;
%mend;
data _null_;
set control;
call execute('%display_val(' !! trim(filename) !! ');');
run;
as it does away with flooding your symbol table with lots of macro vars.
Use options mprint symbolgen to reveral what happens; also use %put to help in diagnosing.
Your code as such looks OK, as this simple test shows:
%let file1=xxxx;
%let file2=yyyy;
%macro display_val(inval);
%put inval=&inval;
%mend;
%macro loop;
%do i = 1 %to 2;
%display_val(&&file&i);
%end;
%mend;
%loop
Personally, I prefer to run such repeats off a dataset:
data control;
input filename $;
cards;
xxxx
yyyy
;
run;
%macro display_val(inval);
%put inval=&inval;
%mend;
data _null_;
set control;
call execute('%display_val(' !! trim(filename) !! ');');
run;
as it does away with flooding your symbol table with lots of macro vars.
Hello Kurt,
with the help of a control data set, your solution looks very elegant and in deed avoids flooding the Macro symbol tables. Thanks for your help!
FK
Hello @Kurt_Bremser
I am trying to transcribe your code to my macro, however, I was wondering about one thing:
Don#t you have to mention the macro parameter "invalue", when calling the macro with the call execute?
%macro display_val(inval);
%put inval=&inval;
%mend;
data _null_;
set control;
call execute('%display_val(' inval = !! trim(filename) !! ');');
run;
Also, what if my Macro, which I want to call in the call execute statement, has more than one parameter? How do I correctlyx mention them in the CALL EXECUTE STATEMENT?
data control2;
format filenames $5. lfd_nr best8. index $1.;
input filenames lfd_nr index ;
cards;
file1 1 A
file2 2 B
file3 3 C
;
run;
%macro display_val(inval1, inval2, inval3);
%put inval1=&inval1;
%put inval2=&inval2;
%put inval3=&inval3;
%mend;
data _null_;
set control;
call execute('%display_val(' inval1 = !! trim(filenames) !! , inval2 = !! trim(var2_from_control)!! , inval3 = !! trim(var3_from_control)!! ');');
run;
d
In my macro definition, inval is a positional parameter, identified by its position in the list; what you are thinking of are keyword parameters (see http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#macro-stmt.htm).
It all depends on your macro definition; with positional parameters, you have to keep the correct order, with keyword parameters, you need to supply their name when calling the macro.
Since I like shorter code, I usually use positional parameters.
You would be better off just reading all the csv file names in one datastep import using wildcards:
This would be far easier for you, you can split the data up later on if you really have to, though working with the same data in one dataset is far easier.
As for your code, how do we know why it does not do what you think it should, you have not provided any test data, or code to run, nor logs or anything else which would help identify problems. For instance would it not be easier just to do:
data _null_; infile datalines; input; call execute(cats('%get_product_data (p_sinoutproductfilecsv=',_infile_,',p_sincsvdelimter={);')); datalines; abc.csv def.csv ; run;
Also, why do you have a parameter for delimiter? You say these files a Comma Separated Variable files, which means the delimiter is a Comma. If it is not then you are not dealing with CSV files.
As @RW9 mentioned, my example is missing the underlying Macro Code. It turned out that I used the same looping index in botch Macros, so that SAS got "confused"
As to the approach with the wild card search, I have the problem, that these "{" delimited files reside on a web page from which I collect them via
filename url "http://www.some.webadress.com/this_is_a_csv.csv";
So just dump the files out locally:
filename url "http://www.some.webadress.com/this_is_a_csv.csv"; data _null_; infile url; file "c:/localcopy/this_is_NOT_a_csv.dlm"; input; put; run;
Then once they are local you can use the wildcard. But its likely the call execute would be better, with a control dataset.
@FK1 wrote:
As @RW9 mentioned, my example is missing the underlying Macro Code. It turned out that I used the same looping index in botch Macros, so that SAS got "confused"
As to the approach with the wild card search, I have the problem, that these "{" delimited files reside on a web page from which I collect them via
filename url "http://www.some.webadress.com/this_is_a_csv.csv";
Hint: always use a %local statement for variables that are needed only inside the macro and not passed as parameters. That prevents accidental manipulation of macro variables in another scope.
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.