DATA Step, Macro, Functions and more

Invoke Macros using Dataset Variable Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Invoke Macros using Dataset Variable Values

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_1Condition2
12
35
46

 

 

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

 

ConditionPersonMerge_Variable
119
129
230
240
258
267
275
384
391
3102
3115
4120
4132
4143
4157
5169
5170
5183
5198
6207
6215

 

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


Accepted Solutions
Solution
‎03-29-2017 11:25 AM
Super User
Posts: 17,962

Re: Invoke Macros using Dataset Variable Values

CALL EXECUTE. See the second example in the documentation. 

View solution in original post


All Replies
Solution
‎03-29-2017 11:25 AM
Super User
Posts: 17,962

Re: Invoke Macros using Dataset Variable Values

CALL EXECUTE. See the second example in the documentation. 

Occasional Contributor
Posts: 16

Re: Invoke Macros using Dataset Variable Values

Thank you very much - just what I needed!
Super User
Posts: 6,972

Re: Invoke Macros using Dataset Variable Values

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 16

Re: Invoke Macros using Dataset Variable Values

Thank you very much - this is a great alternative solution!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 131 views
  • 2 likes
  • 3 in conversation