topic Re: Compare data value from 2 datasets in SAS Enterprise Guide

Compare data value from 2 datasets

Neha2 — Sat, 18 Mar 2017 20:08:51 GMT

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

Re: Compare data value from 2 datasets

Kurt_Bremser — Tue, 14 Mar 2017 10:04:27 GMT

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

Re: Compare data value from 2 datasets

Neha2 — Tue, 14 Mar 2017 10:08:30 GMT

I need only incorrect data.

Re: Compare data value from 2 datasets

Kurt_Bremser — Tue, 14 Mar 2017 11:39:26 GMT

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

Re: Compare data value from 2 datasets

ballardw — Tue, 14 Mar 2017 14:10:37 GMT

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

Re: Compare data value from 2 datasets

Neha2 — Sat, 18 Mar 2017 10:21:42 GMT

Thanks. Its working.

Re: Compare data value from 2 datasets

Neha2 — Sat, 18 Mar 2017 10:23:47 GMT

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

Re: Compare data value from 2 datasets

Kurt_Bremser — Sat, 18 Mar 2017 12:33:02 GMT

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-data-AKA-generate/ta-p/258712 to convert your example dataset into a data step for posting here.