Variation of a question related to "ERROR: Sort execution failure."

[accintron]

Long story short, I keep getting the "ERROR: Sort execution failure" when running the code below. I know why I'm running into the issue, but can't wrap my head around how to fix it. The problem is that SYMPTOM1_TRXN_ID has many unique values that match to the SYMPTOM1_OTHER column (as you see in a quick snapshot of the table below).

My code:

proc sql;
create table SYMPTOMS as
select a.ADMISSION_ID, b.SYMPTOM1_TYPE_ID, b.SYMPTOM1_OTHER, b.SEVERITY_LEVEL_ID
from open_adms8 a left join cairs.SYMPTOM1_TRXN b on a.SYMPTOM1_TRXN_ID = b.SYMPTOM1_TRXN_ID;
quit;

Just a quick example: you can see that when SYMPTOM1_TRXN_ID 1 or 2 and they both have SYMPTOM1_OTHER = Suicidal ideation.... How do I handle this if I need to match two tables on SYMPTOM1_TRXN_ID because that is the identifier to join the tables?

Thanks in advance.

[SASKiwi]

To get a unique row in the table you've displayed you need to take SYMPTOM1_TYPE_ID into account too. Otherwise for every row in your admission table where SYMPTOM1_TRXN_ID = 1 you will get 25 symptom rows being joined in. Do you really want that?