SAS merging multiple tables

Question

I would like to know what is the best way to merge multiple tables. I have a unique identifiers across all the tables. Should I join all the tables in one step after sorting the tables OR should I should do stepwise one by one table merging. Does this matter ?

Answer 1

You can do multiple merges at single step. However, this is not the safest way. If there is possibility that your data is subject to imperfections, it is best to do this step by step. Imho, it is best do merge a step at the time, but it's your call.

proc sort data=data1; by id; run;
proc sort data=data2; by id; run;
proc sort data=data3; by id; run;

data combo;
    merge data1(in=a) data2(in=b) data3(in=c); 
    by id; 
    if a and b and c; /*Inner join. Change as needed. */
run;

This is equivalent to:

data partial;
    merge data1(in=a) data2(in=b);
    by id; 
    if a and b; 
run;

data combo;
    merge partial(in=a) data3(in=b);
    by id; 
    if a and b;
run,

Answer 2

There's no particular reason to do it step-by-step, unless you've got conflicting variable names that you're concerned about resolving, or if your combination logic is complicated and you're worried about confusing something. There's no functional reason why not, in any event. merge in SAS is actually somewhat simpler than join in SQL, in particular as the syntax is simpler, so it's somewhat different than the SQL case.