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
CALL EXECUTE. See the second example in the documentation.
CALL EXECUTE. See the second example in the documentation.
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.
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.
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.