BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HoaTruong
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

CALL EXECUTE. See the second example in the documentation. 

View solution in original post

4 REPLIES 4
Reeza
Super User

CALL EXECUTE. See the second example in the documentation. 

HoaTruong
Obsidian | Level 7
Thank you very much - just what I needed!
Kurt_Bremser
Super User

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
Obsidian | Level 7
Thank you very much - this is a great alternative solution!

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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