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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.