topic Merging Multiple Datasets in SAS Programming

Merging Multiple Datasets

milts — Mon, 04 Apr 2016 02:22:56 GMT

I have tables with these structure, two IDs and a score.

id_A,id_B,score_01

id_A,id_B,score_02

id_A,id_B,score_03

id_A,id_B,score_04

id_A,id_B,score_0n

What's the most efficient way to combine so that i have a structure

id_A,id_B,score_01,score_02,score_03,score_04,score_0n?

Not that the combination of id_A and id_B may not be existing in all datasets.

Thank you very much!

Re: Merging Multiple Datasets

Reeza — Mon, 04 Apr 2016 02:42:47 GMT

Do you have multiple records per IDs? Is it a one to one join, or many to many join?

Re: Merging Multiple Datasets

milts — Mon, 04 Apr 2016 03:13:03 GMT

For each dataset, there is only one instance of the combination of an id_A and id_B.

Currently I'm using full outer join but I think there's a more efficient way.

Re: Merging Multiple Datasets

Reeza — Mon, 04 Apr 2016 03:16:46 GMT

If you want something more efficient than your code, post your code.

You can try a data step merge.

data want;
merge data1 (rename=score=score1)
           data2 (rename=score=score2)
           ....
           data6 (rename=score=score6);
by idA idB;


run;

Re: Merging Multiple Datasets

milts — Mon, 04 Apr 2016 03:44:07 GMT

Thanks for the idea. My code previously was like using full outer join like these

select coalesce(a.id_A,b.id_A,....j.id_A) as id_A,
select coalesce(a.id_B,b.id_B,....j.id_B) as id_B,
score_1,...score_10

Re: Merging Multiple Datasets

RW9 — Mon, 04 Apr 2016 08:23:44 GMT

As always in these type of questions, my first response is, is the strcuture you require the best fit from a programming perspective. The answer here is no. Personally I would set these together and get one long table with each row being a separate result. This simplfies any coding you need to do across the variables, and seeing as the strcuture wouldn't change if the number of items changed (i.e. increase or decrease) then the maintenance of such code also becomes far easier. I find transposed data is only useful in output reports.