topic Re: Mapping datavalues from 2 different datasets in SAS Enterprise Guide

Mapping datavalues from 2 different datasets

india2016 — Mon, 20 Mar 2017 10:31:14 GMT

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
District	Circle	Station	District	Circle	Station
EAST	ADARSH NAGAR	ADARSH NAGAR	EAST	CHOMU	ADARSH NAGAR
EAST	ADARSH NAGAR	JAWAHAR NAGAR	EAST	ADARSH NAGAR	JAWAHAR NAGAR
WEST	CHOMU	HARMARA	WEST	CHOMU	HARMARA
WEST	CHOMU	VISHWAKARMA	EAST	CHOMU	VISHWAKARMA

Re: Mapping datavalues from 2 different datasets

LinusH — Mon, 20 Mar 2017 11:20:21 GMT

Please show a desired output to further describe your requirement.

Re: Mapping datavalues from 2 different datasets

Kurt_Bremser — Mon, 20 Mar 2017 12:57:57 GMT

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-data-AKA-generate/ta-p/258712

Re: Mapping datavalues from 2 different datasets

india2016 — Wed, 22 Mar 2017 05:43:08 GMT

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;

Re: Mapping datavalues from 2 different datasets

india2016 — Wed, 22 Mar 2017 05:44:30 GMT

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;

Re: Mapping datavalues from 2 different datasets

india2016 — Wed, 22 Mar 2017 05:49:48 GMT

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

Re: Mapping datavalues from 2 different datasets

Kurt_Bremser — Wed, 22 Mar 2017 07:16:28 GMT

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;