[FIXED] How to merge two tables using SAS proc sql

Issue

Here is the exam data table that I would like to process here:

-origin data

--a table

STU_ID	STU_KEY	STU_CODE_1
123	2002123	A121
124	2002124	A122
125	2002125	A123
126	2002126	A124

--b table |STU_ID|STU_KEY|STU_CODE_2| |------|-------|----------| |123 |2002223|B121 | |124 |2002224|B122 | |125 |2002225|C123 | |126 |2002226|C124 | |127 |2002127|C125 |

I want to merge between 'a' and 'b' tables using code of SAS proc sql as follows.

-result data merge table

STU_ID	STU_KEY	STU_CODE_1	STU_CODE_2
123	2002123	A121
123	2002223		B121
124	2002124	A122
124	2002224		B122
125	2002125	A123
125	2002225		C123
126	2002126	A124
126	2002226		C124
127	2002127		C125

 '''error code'''
 PROC SQL;
 CREATE TABLE a_b_merge as
 SELECT t1.STU_ID, 
        t1.STU_KEY, 
        t1.STU_CODE_1
        t2.STU_CODE_2
 FROM a as t1 full join b as t2
 ON t1.STU_ID=t2.STU_ID
 QUIT;  

Let me know how to solve this problem.

Solution

There are several syntax errors in your query:

• A missing , after t1.STU_CODE_1
• A missing ; before the quit;

In order to get the expected result, use OUTER UNION CORR. OUTER UNION keep all rows and all columns from the two intermediate result sets and CORR causes PROC SQL to match the columns in table-expressions by name. Also add an ORDER BY to match the expected output.

data table_a;
infile datalines4 delimiter="|";
input stu_id stu_key stu_code_1 :$8.;
datalines4;
123|2002123|A121
124|2002124|A122
125|2002125|A123
126|2002126|A124
;;;;

data table_b;
    infile datalines4 delimiter="|";
    input stu_id stu_key stu_code_2 :$8.;
    datalines4;
123|2002223|B121
124|2002224|B122
125|2002225|C123
126|2002226|C124
127|2002227|C125
;;;;

proc sql;
    create table want as
    select * from table_a 
    outer union corr
    select * from table_b
    order by stu_id, stu_key;
quit;

Answered By - Kermit

Answer Checked By - Candace Johnson (PHPFixing Volunteer)