PROC SQL: How to Identify and exclude Error records from a sales table

[tnachis]

I have two table, (Standard Table) containing "Orig ID" column and "ID" column. One "ID" can have multiple "Orig ID" values.
The second table is the (Sales table) containing "Orig ID" column and "ID" column. The "Orig ID" and "ID" vales in the Sales table must match/follow the order in the Standard Table.

Note:
All "ID" in the Sales table already exist in the Standard Table.
There can be new "Orig ID" value created in the Sales table that does not have a matching "ID" in the  Standard Table.
One of the "Orig ID" value in the Sales table was assigned a wrong "ID" 

Target:
I would like to Join the (Sales table) to the (Standard Table) on Mapped To and ID.
Then, identify and exclude all values in the (Sales table) that do not match the order in the Standard Table.
Also, Identify the New "Orig ID" values created in the Sales table that are not yet recognized in the Standard Mapping Table

Standard Table:

Orig ID                 ID

AB                      A20

BC                     A20

CD                      A20

JK                      B30

JL                       B30

Sales Table

Orig ID               ID 

AB                     A20

BC                    B30

JK                     JK

Desired Outcome:

I want to create the dataset "error_table" containing obs with matching "Orig ID" but non-matching "id", and another dataset "NEW ID Table" containing those obs from "sales" that the value in "orig Id" is same as "ID" (this is because the new "Orig id" created in the sales table is always same as the "ID").  

Error Table:

Sales Table

Orig ID               ID 

BC                    B30

NEW ID Table:

Sales Table

Orig ID               ID 

JK                       JK

[ballardw · Accepted solution]

I might try:

proc sql;
   create table salesNotStandard as
   select distinct origid, id from salestable
   except
   select distinct origid, id from standardtable
   ;
   create table StandardNotsales as
   select distinct origid, id from standardtable
   except
   select distinct origid, id from salestable
   ;
quit;

I didn't use your table names as I wasn't quite sure which is really an "error". So use more descriptive "SalesNotStandard" means the id combination appears in Sales but Not the Standard data set.

---

@tnachis wrote:

I have two table, (Standard Table) containing "Orig ID" column and "ID" column. One "ID" can have multiple "Orig ID" values.
The second table is the (Sales table) containing "Orig ID" column and "ID" column. The "Orig ID" and "ID" vales in the Sales table must match/follow the order in the Standard Table.

Note:
All "ID" in the Sales table already exist in the Standard Table.
There can be new "Orig ID" value created in the Sales table that does not have a matching "ID" in the  Standard Table.
One of the "Orig ID" value in the Sales table was assigned a wrong "ID" 

Target:
I would like to Join the (Sales table) to the (Standard Table) on Mapped To and ID.
Then, identify and exclude all values in the (Sales table) that do not match the order in the Standard Table.
Also, Identify the New "Orig ID" values created in the Sales table that are not yet recognized in the Standard Mapping Table

Standard Table:

Orig ID                 ID

AB                      A20

BC                     A20

CD                      A20

JK                      B30

JL                       B30

Sales Table

Orig ID               ID 

AB                     A20

BC                    B30

JK                     JK

Desired Outcome:

I want to create the dataset "error_table" containing obs with matching "Orig ID" but non-matching "id", and another dataset "NEW ID Table" containing those obs from "sales" that the value in "orig Id" is same as "ID" (this is because the new "Orig id" created in the sales table is always same as the "ID").  

Error Table:

Sales Table

Orig ID               ID 

BC                    B30

NEW ID Table:

Sales Table

Orig ID               ID 

JK                       JK

---