topic Re: conditional joining of multiple files in SAS Programming

conditional joining of multiple files

makset — Fri, 18 Sep 2020 12:29:12 GMT

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.

Re: conditional joining of multiple files

gamotte — Fri, 18 Sep 2020 12:35:13 GMT

Hello,

Does the condition depend on &i. ?

If not, can't you just do :

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

Re: conditional joining of multiple files

Shmuel — Fri, 18 Sep 2020 12:41:50 GMT

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;

Re: conditional joining of multiple files

makset — Fri, 18 Sep 2020 12:45:00 GMT

unfortunately it depends on &i

Re: conditional joining of multiple files

makset — Fri, 18 Sep 2020 12:46:39 GMT

small correction, condition 'if' depends on &i

Re: conditional joining of multiple files

Shmuel — Fri, 18 Sep 2020 14:00:38 GMT

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

Re: conditional joining of multiple files

ballardw — Fri, 18 Sep 2020 14:23:04 GMT

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.

Re: conditional joining of multiple files

Shmuel — Fri, 18 Sep 2020 14:47:34 GMT

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;