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

PROC SQL: How to Identify and exclude wrong records

tnachis — Fri, 10 Dec 2021 16:12:52 GMT

i have two table, (Standard Mapping Table) containing "Mapped To" column and "ID" column. One "ID" can have multiple "Mapped To" values.
The second table is the (Sales table) containing "Mapped To" column and "ID" column. The "Mapped To" and "ID" vales in the Sales table must match/follow the order in the Standard Mapping Table. 
 
Note:

All "ID" in the Sales table already exist in the Standard Mapping Table.
There can be new "Mapped To" value created in the Sales table that does not have a matching "ID" in the Standard Mapping Table. 
The "Mapped To" value in the Sales table was assigned wrong "ID"
 
Target:

I would like to Join the (Sales table) to the (Standard Mapping 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 Mapping Table. 
Also, Identify the New "Mapped To" values created in the Sales table that are not yet recognized in the Standard Mapping Table
 
Standard Mapping Table
Mapped To                            ID                                         
AB                                        D20  
BC                                        D20
CA                                        D20
DE                                       A30
EF                                       A30
 
Sales Table 
Mapped To                           ID
AB                                      D20
BC                                      A30
DE                                      A30
JK                                       JK
 
Desired Result:
 
Sales Table1 (Error Mapping)
Mapped To                           ID
BC                                      A30
 
Sales Table 2 (New Mapped To and ID created)
Mapped To                           ID
JK                                       JK
 
Please can you help? 
          

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

andreas_lds — Fri, 10 Dec 2021 07:02:12 GMT

In other words: you want to create the dataset "error_mapping" containing obs with matching "mapped_to" but non-matching "id", and another dataset with containing those obs from "sales" that don't match any "mapped_to" in your "standard_mapping" dataset. Right?

I don't understand what "... that do not match the order in the Standard Mapping Table" in the second target means.

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

ballardw — Fri, 10 Dec 2021 08:09:45 GMT

Please look at you posted "data".

Please post it in some legible form.

Best way to provide example data, especially if you would like working code examples or code that may work, is to provide data in the form of data step code.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

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

tnachis — Fri, 10 Dec 2021 09:51:27 GMT

Andreas, yes you're correct. I want to create the dataset "error_mapping" containing obs with matching "mapped_to" but non-matching "id", and another dataset with containing those obs from "sales" that don't match any "mapped_to" in your "standard_mapping" datase.

While I was trying to update the data table, the question got corrupted. I will re-write the question and post again. Thank you