Desktop productivity for business analysts and programmers

Compare data value from 2 datasets

Reply
Occasional Contributor
Posts: 7

Compare data value from 2 datasets

[ Edited ]

 I have 2 datasets. In one dataset we have correct data for example: states falls in country variable. and in another dataset we have incorrect data for example few of the states falls into wrong country. I want to check that incorrect data. How to do that??

Esteemed Advisor
Posts: 6,646

Re: Compare data value from 2 datasets

Try this:

data correct;
input country :$20. state :$20.;
cards;
US Nevada
Austria Tirol
US California
;
run;

data dubious;
input country :$20. state :$20.;
cards;
UK Nevada
Austria Tirol
France California
;
run;

proc sort data=correct;
by state;
run;

proc sort data=dubious;
by state;
run;

data checked;
merge
  dubious (in=d)
  correct (in=c rename=(country=_country))
;
by state;
if d;
if c and country ne _country then country = _country;
drop _country;
run;

proc print data=checked noobs;
run;

This results in:

country    state

US         California
US         Nevada    
Austria    Tirol     
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Compare data value from 2 datasets

I need only incorrect data. 

Esteemed Advisor
Posts: 6,646

Re: Compare data value from 2 datasets

Simple change, you should be able to find this yourself:

data checked;
merge
  dubious (in=d)
  correct (in=c rename=(country=_country))
;
by state;
if d;
if c and country ne _country;
run;

The result now is this:

country    state         _country

France     California       US   
UK         Nevada           US   
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Compare data value from 2 datasets

Thanks. Its working. 

Occasional Contributor
Posts: 7

Re: Compare data value from 2 datasets

Can we do the same for 3 variables?? As in we have one more variable name city it should also mapped correctly. I tried but then its not working. Can we do that by creating macro??
Esteemed Advisor
Posts: 6,646

Re: Compare data value from 2 datasets

Just to repeat what @ballardw said:

"It is a very good idea to provide some example data and the final result for your example data."

 

Don't make us do all your work for you.

 

Use the macro provided here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your example dataset into a data step for posting here.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 10,210

Re: Compare data value from 2 datasets

It is a very good idea to provide some example data and the final result for your example data.

Ask a Question
Discussion stats
  • 7 replies
  • 177 views
  • 1 like
  • 3 in conversation