Desktop productivity for business analysts and programmers

Mapping datavalues from 2 different datasets

Reply
Contributor
Posts: 42

Mapping datavalues from 2 different datasets

I have 2 dataset described below name correct data and incorrect data. Now i want to map incorrect data from correct data.

For example in incorrect data for adarsh nagar station circle is incorrect and for harmara station police district is incorrect.

I have used proc sort by station for both the table and then i have used IN= option but it wokrs for just one variable but i want to map data using for both variable i.e. circle and station.

 

Correct Data Incorrect data
DistrictCircleStation DistrictCircleStation
EASTADARSH NAGARADARSH NAGAR EASTCHOMUADARSH NAGAR
EASTADARSH NAGARJAWAHAR NAGAR EASTADARSH NAGARJAWAHAR NAGAR
WESTCHOMUHARMARA WESTCHOMUHARMARA
WESTCHOMUVISHWAKARMA EASTCHOMUVISHWAKARMA
Esteemed Advisor
Posts: 5,198

Re: Mapping datavalues from 2 different datasets

Please show a desired output to further describe your requirement.

Data never sleeps
Contributor
Posts: 42

Re: Mapping datavalues from 2 different datasets

input district :$10. circle :$20. station :$20.;
cards;
east adarshnagar adarshnagar
east adarshnagar jawaharnagar
east gandhinagar gandhinagar
west chomu harmara
west chomu vishwakarma
;
run;
data incorrect;
infile cards;
input district :$10. circle :$20. station :$20.;
cards;
east chomu adarshnagar
east adarshnagar jawaharnagar
west chomu harmara
east chomu vishwakarma
;
run;
Contributor
Posts: 42

Re: Mapping datavalues from 2 different datasets

This is my program that i had done

proc sort data=WORK.CORRECT;
by station;
run;
proc sort data=WORK.INCORRECT;
by station;
run;

data match mismatch;
merge WORK.CORRECT(in=PC) WORK.INCORRECT(in=PS1 rename=(Circle=Circle1 district=district1));
by station;
if PC;
if district eq district1 and Circle eq Circle1 then output match;
if district ne district1 and Circle ne Circle1 then output mismatch;
run;
Contributor
Posts: 42

Re: Mapping datavalues from 2 different datasets

This is what i am getting in output for match
district,circle,station,district1,Circle1
west,chomu,harmara,west,chomu
east,adarshnagar,jawaharnagar,east,adarshnagar



Output for mismatch

district,circle,station,district1,Circle1
east,gandhinagar,gandhinagar,,


Match is correct but i am not getting correct data for mismatch
Esteemed Advisor
Posts: 6,684

Re: Mapping datavalues from 2 different datasets

[ Edited ]

Maybe you should slightly change the second condition:

data match mismatch;
merge WORK.CORRECT(in=PC) WORK.INCORRECT(in=PS1 rename=(Circle=Circle1 district=district1));
by station;
if PC;
if district eq district1 and Circle eq Circle1 then output match;
if district ne district1 or Circle ne Circle1 then output mismatch;
run; 

or make it even simpler:

data match mismatch;
merge WORK.CORRECT(in=PC) WORK.INCORRECT(in=PS1 rename=(Circle=Circle1 district=district1));
by station;
if PC;
if district eq district1 and Circle eq Circle1
then output match;
else output mismatch;
run; 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Posts: 6,684

Re: Mapping datavalues from 2 different datasets

Please post example data in a data step, like

data correct;
infile cards dlm=',';
input district :$4. circle :$20. station :$20.;
cards;
EAST,ADARSH NAGAR,ADARSH NAGAR
EAST,ADARSH NAGAR,JAWAHAR NAGAR
WEST,CHOMU,HARMARA
WEST,CHOMU,VISHWAKARMA
;
run;

This makes it easier for us to recreate your data.

A macro that does this automatically cam be found at https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 6 replies
  • 129 views
  • 1 like
  • 3 in conversation