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

tnachis — Fri, 10 Dec 2021 16:11:06 GMT

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

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

ballardw — Fri, 10 Dec 2021 16:33:26 GMT

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

topic PROC SQL: How to Identify and exclude Error records from a sales table in SAS Programming

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

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