DATA Step, Macro, Functions and more

Compare two variables

Reply
Super Contributor
Posts: 647

Compare two variables

There are two tables.Price dataset and Product dataset.
Recently I came across an issue where the product name in the price list has minor variation from the same product name in product dataset.This is causing drop in sales.

for instance:
price dataset has product name as CANDOR LEVODOPA 10MG.
product dataset has product name as CANDOR LEVODOPA 10MG.

not only that misspelling CANDOR LEVIDOPA 10MG.

How to compare character by character and identify where there is difference?
Occasional Contributor
Posts: 7

Re: Compare two variables

Hi,

You have to do some data cleansing manually.
However, I would suggest you to try sounds-like operator to compare product_name variables in two tables. Refer to:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473716.htm

I have tried a sample code to test this.
/* -------------------------------------------------------------------------------- */
data price;
product_name='CANDOR LEVODOPA 10MG';
run;

data product;
product_name='CANDOR LEVIDOPA 10MG';
run;

proc sql;
/* The following sql won't return any row when using 'equal to i.e. =' operator in where clause */
create table compare_price_product_eq as
select pri.product_name as pri_product_name, pro.product_name as pro_product_name
from price pri, product pro
where pri.product_name = pro.product_name;

/* The following sql will return a row when using 'sound like i.e. =*' operator in where clause */
create table compare_price_product_sl as
select pri.product_name as pri_product_name, pro.product_name as pro_product_name
from price pri, product pro
where pri.product_name =* pro.product_name;
quit;


/* -------------------------------------------------------------------------------- */
Super User
Posts: 9,681

Re: Compare two variables

Check function compare();


Ksharp
Ask a Question
Discussion stats
  • 2 replies
  • 207 views
  • 0 likes
  • 3 in conversation