BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

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.

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You would be better off just reading all the csv file names in one datastep import using wildcards:

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

 

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.

FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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";
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1080 views
  • 2 likes
  • 3 in conversation