Invoke Macros using Dataset Variable Values

[HoaTruong]

Hi,

Please can you help with the following query.

I have a 'control' dataset with two variables Condition_1 and Condition_2.  The table looks a bit like the following example:

Condition_1	Condition2
1	2
3	5
4	6

I also have a 'base' dataset that resembles the following:

Condition	Person	Merge_Variable
1	1	9
1	2	9
2	3	0
2	4	0
2	5	8
2	6	7
2	7	5
3	8	4
3	9	1
3	10	2
3	11	5
4	12	0
4	13	2
4	14	3
4	15	7
5	16	9
5	17	0
5	18	3
5	19	8
6	20	7
6	21	5

I have a macro that takes two parameters and creates a new dataset.  The new dataset is the result of merging two datasets - one that is created from the base table by filtering by Condition = &a; the other is created from the base table by filtering by Condition = &b.

%MACRO MERGE(a, b);

Data temp1;

Set BASE_TABLE (Where = (Condition = &a));

Run;

Data temp2;

Set BASE_TABLE (Where = (Condition = &b));

Run;

Proc Sort Data = temp1; By Merge_Variable; Run;

Proc Sort Data = temp2; By Merge_Variable; Run;

Data Combined_&a._&b;

Merge temp1 (In = A)

temp1 (In = B)

;

By Merge_Variable;

If A AND B;

Run;

%MEND MERGE;

Please can you help me write some code that takes each row of the 'control' table, and invokes this MERGE macro - by passing the values of variables Condition_1 and Condition_2.

Many thanks,

Hoa

[Reeza · Accepted solution]

CALL EXECUTE. See the second example in the documentation. 

[HoaTruong]

Thank you very much - just what I needed!

[Kurt_Bremser]

This might solve your problem without use of a macro:

data base;
input Condition Person Merge_Variable;
cards;
1 1 9
1 2 9
2 3 0
2 4 0
2 5 8
2 6 7
2 7 5
3 8 4
3 9 1
3 10 2
3 11 5
4 12 0
4 13 2
4 14 3
4 15 7
5 16 9
5 17 0
5 18 3
5 19 8
6 20 7
6 21 5
;
run;

data control;
input cond1 $ cond2 $;
cards;
1 2
3 5
4 6
;
run;

* create cartesian product;

proc sql;
create table base1 as
select
  a.condition as cond1,
  b.condition as cond2,
  a.person as person1,
  b.person as person2,
  a.merge_variable
from
base a, base b
where a.merge_variable = b.merge_variable
;
quit;

data _null_;
call execute("data ");
do until (eof1);
  set control end=eof1;
  call execute("combined_"!!trim(cond1)!!"_"!!trim(cond2)!!" ");
end;
call execute("; set base1;");
do until (eof2);
  set control end=eof2;
  call execute("if cond1 = "!!trim(cond1)!!" and cond2 = "!!trim(cond2)!!" then output combined_"!!trim(cond1)!!"_"!!trim(cond2)!!";");
end;
call execute("run;");
stop;
run;

Note that only conditions 4 and 6 have a merge_variable (7) in common, so combined_4_6 has one result, the other datasets are empty.

[HoaTruong]

Thank you very much - this is a great alternative solution!