conditional joining of multiple files

[makset]

We use the following scheme many times in my programs. I am retrieving data from multiple tables that meet certain conditions and a link to the final table. Many times the loop has over 1000 iterations and it works forever :). It can be optimized somehow, reducing the operating time.
The code below is psudo code and I hope it is readable.

%let list=a b c d e f g h i j k l m n o p; *	and so on;
%let nwords=%sysfunc(countw(&list));

%macro loop();
%do i = 1 %to &nwords;
	%if &i = 1 %then %do; 

		data test;
		set %scan(&list, &i);
		if /*condition/**/;
		run;

	%end;
	%else %do;

		data temp;
		set %scan(&list, &i);
		if /*condition/**/;
		run;

		data test;
		set test temp;
		run;

	%end;
%end;
%mend loop;

%loop;

Best regards.
Thank you for your help.

[gamotte]

Hello,

Does the condition depend on &i. ?

If not, can't you just do :

data test;
    set &list.;
    if /* condition */
run;

[makset]

unfortunately it depends on &i

[Shmuel]

Try next code:

%let list=a b c d e f g h i j k l m n o p; *	and so on;
%let nwords=%sysfunc(countw(&list));

%macro loop();
%do i = 1 %to &nwords;
	%if &i = 1 %then %do; 
	    proc datasets lib=work; delete test; quit;
	%end;
        proc append base=test 
		     data=%scan(&list, &i) 
			      (where=(<condition))  FORCE;
		run;		  
%end;
%mend loop;

%loop;

[makset]

small correction, condition 'if' depends on &i

[Shmuel]

---

@makset wrote:
small correction, condition 'if' depends on &i

---

You can assign the conditions in macro variables per input then use next adjusted code

%let list=a b c d e f g h i j k l m n o p; *	and so on;
%let nwords=%sysfunc(countw(&list));
%let cond1 = <condition foe dataset 1>;
...

%macro loop();
%do i = 1 %to &nwords;
	%if &i = 1 %then %do; 
	    proc datasets lib=work; delete test; quit;
	%end;
        proc append base=test 
		     data=%scan(&list, &i) 
			      (where=(&&cond&i)  FORCE;
		run;		  
%end;
%mend loop;

%loop;

 If conditions are constants and depended on the dataset you can enter them in a table

in order to create the macro variables &&cond&i grammatically..

[ballardw]

Warning: bad things eventually happen to people that rely on code that has macro variables just "appear" in the body of a macro.

You  would be much better off defining your macro with an actual parameter to contain the list and count the elements internally in the macro.Even if the macro variable such as your list already exists you can pass it as a parameter such as

%macro loop (locallist = &list);

then use &locallist in a local countw and scan.

One of things that can happen with the "drop in" macro variables is you may change the value of list and elsewhere the change is used when you may not intend it.

[Shmuel]

What about maintaining a table - maybe by excel - containing few columns:

1) input dataset name 

2) condition for sub-setting relevant observations

3) yes/no flag - should this row participate in current run

then transfer the data from the table to macro variables to be used with

macro %loop or alternatively use call execute to submit the code to run

adapted to current row, or even generate code like @gamotte posted:

data want
  set <input1>(where=(<condition1>))
       <input2>(where=(<condition2>))
      ....
;
run;