Solved
New Contributor
Posts: 2

# Proc SQL merge and find mismatch

Hi,

I would like to find the exact mismatch and missing values from the below Tables A and B by using Proc SQL procedure, Can anyone help me please?

Table A

 Code Test Value 1001 ABCD Good 1001 ABCD Bad 1001 ABCD Above average 1001 ABCD Average

Table B

 Code Test Value1 1001 ABCD Bad 1001 ABCD Good 1001 ABCD 1001 ABCD Below average

Expected output:

 Code Test Value Value1 1001 ABCD Above average Below average 1001 ABCD Average

Thanks,

Abin

Accepted Solutions
Solution
‎10-07-2015 02:39 PM
Contributor
Posts: 66

## Re: Proc SQL merge and find mismatch

[ Edited ]

Here's the output for each of the four PROC SQL statements:

 Table_A_Not_B Obs Code Test Value seq_num 1 1001 ABCD Above_average 1 2 1001 ABCD Average 2 Table_B_Not_A Obs Code Test Value1 seq_num 1 1001 ABCD Below_average 1

 Want_Side_by_Side Obs Code Test Value Value1 seq_num 1 1001 ABCD Above_average Below_average 1 2 1001 ABCD Average 2

 Want_Stacked Obs Code Test Value Row_Source 1 1001 ABCD Above_average Table_A_Not_B 2 1001 ABCD Average Table_A_Not_B 3 1001 ABCD Below_average Table_B_Not_A

All Replies
Super User
Posts: 13,546

## Re: Proc SQL merge and find mismatch

How are we supposed to know that "Below Average" is the mismatch for "Above average" and not Average?

That this is NOT the desired result?

 Code Test Value Value1 1001 ABCD Above average 1001 ABCD Average Below average

You might want to provide a bit more data and possbily with more Code and Test values as currently we don't know if there are any and solutions if ALL of Code = 1001 and All of Test=ABCD may not work if there are others involved.

New Contributor
Posts: 2

## Re: Proc SQL merge and find mismatch

Thank you Ballardw!

Yes, its also possible!

But i don't want to see somehitng like

 Code Test Value Value1 1001 ABCD good Bad 1001 ABCD Bad good
Posts: 5,529

## Re: Proc SQL merge and find mismatch

[ Edited ]

I suggest to list the mismatches in the following way:

``````
title "Unmatched values";
proc sql;
select "A" as from, a.Code, a.Test, a.Value
from a left join b
on a.code=b.code and a.test=b.test and a.value=b.value
where b.value is missing
union all
select "B" as from, b.Code, b.Test, b.Value
from a right join b
on a.code=b.code and a.test=b.test and a.value=b.value
where a.value is missing
order by code, test, from
;
quit;``````
PG
Contributor
Posts: 66

## Re: Proc SQL merge and find mismatch

[ Edited ]

This could be done in fewer steps, but I used multiple PROC SQL statements to show what is done at each step to arrive at the final results.

[Edited: you may want to change "EXCEPT ALL" to "EXCEPT". If your data contains multiple rows with identical values (e.g. three observations in Table_A that all have "1001 / ABCD / Good"), "EXCEPT" should give you better results.]

``````DATA Table_A;
LENGTH Code 8 Test \$10 Value \$20;
INPUT Code Test \$ Value \$;
DATALINES;
1001 ABCD Good
1001 ABCD Above_average
1001 ABCD Average
;
RUN;

DATA Table_B;
LENGTH Code 8 Test \$10 Value1 \$20;
INPUT Code Test \$ Value1 \$;
DATALINES;
1001 ABCD Good
1001 ABCD .
1001 ABCD Below_average
;
RUN;

PROC SQL;
CREATE TABLE Table_A_Not_B AS
SELECT Qry.*
, monotonic() AS seq_num
FROM (SELECT * FROM Table_A(WHERE=(Value IS NOT NULL))
/* Change "EXCEPT ALL" to "EXCEPT" */
EXCEPT
SELECT * FROM Table_B(WHERE=(Value1 IS NOT NULL))) AS Qry;
QUIT;

PROC SQL;
CREATE TABLE Table_B_Not_A AS
SELECT Qry.*
, monotonic() AS seq_num
FROM (SELECT * FROM Table_B(WHERE=(Value1 IS NOT NULL))
/* Change "EXCEPT ALL" to "EXCEPT" */
EXCEPT
SELECT * FROM Table_A(WHERE=(Value IS NOT NULL))) AS Qry;
QUIT;

/* this would give you the results exactly the way you want (side-by-side) */
PROC SQL;
CREATE TABLE Want_Side_by_Side AS
SELECT COALESCE(T1.Code, T2.Code) AS Code
, COALESCE(T1.Test, T2.Test) AS Test
, T1.Value
, T2.Value1
, COALESCE(T1.seq_num, T2.seq_num) AS seq_num
FROM Table_A_Not_B AS T1
FULL OUTER JOIN Table_B_Not_A AS T2
ON  (T1.Code        = T2.Code
AND T1.Test    = T2.Test
AND T1.seq_num = T2.seq_num);
QUIT;

/* alternate version: this would give you the results vertically (stacked);
this approach would be more traditional in SQL because it avoids assigning
and match/mismatch preference (Ballardw had pointed this out above) */
PROC SQL;
CREATE TABLE Want_Stacked(drop=seq_num) AS
SELECT T1.*, 'Table_A_Not_B' AS Row_Source FROM Table_A_Not_B AS T1
UNION
SELECT T2.*, 'Table_B_Not_A' AS Row_Source FROM Table_B_Not_A AS T2;
QUIT;``````

Hope this helps.

-- hbi

Solution
‎10-07-2015 02:39 PM
Contributor
Posts: 66

## Re: Proc SQL merge and find mismatch

[ Edited ]

Here's the output for each of the four PROC SQL statements:

 Table_A_Not_B Obs Code Test Value seq_num 1 1001 ABCD Above_average 1 2 1001 ABCD Average 2 Table_B_Not_A Obs Code Test Value1 seq_num 1 1001 ABCD Below_average 1

 Want_Side_by_Side Obs Code Test Value Value1 seq_num 1 1001 ABCD Above_average Below_average 1 2 1001 ABCD Average 2

 Want_Stacked Obs Code Test Value Row_Source 1 1001 ABCD Above_average Table_A_Not_B 2 1001 ABCD Average Table_A_Not_B 3 1001 ABCD Below_average Table_B_Not_A

Valued Guide
Posts: 863

## Re: Proc SQL merge and find mismatch

proc sql;
create table want as
select distinct a.code,a.test,a.value,b.value1
from table_a a left join
table_b b on
a.code = a.code and
b.test = b.test
where value not in (select b.value1 from table_b b) and
value1 not in (select a.value from table_a a);

🔒 This topic is solved and locked.