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!
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;
Do you have multiple records per IDs? Is it a one to one join, or many to many join?
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.
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;
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.