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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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