BookmarkSubscribeRSS Feed
Neha2
Calcite | Level 5

 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??

7 REPLIES 7
Kurt_Bremser
Super User

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     
Neha2
Calcite | Level 5

I need only incorrect data. 

Kurt_Bremser
Super User

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   
Neha2
Calcite | Level 5

Thanks. Its working. 

Neha2
Calcite | Level 5
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??
Kurt_Bremser
Super User

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.

ballardw
Super User

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2776 views
  • 1 like
  • 3 in conversation