DATA Step, Macro, Functions and more

Matching and Non-matching

Reply
Super Contributor
Posts: 647

Matching and Non-matching

dataset A has id's with multiple zipcodes.and dataset B can have multiple zipcodes for an id.
how to create matching dataset if:
atleast one of the zip match for an id from dataset A match with dataset B.
for instance id 40288, 95126 matches.so we need to show only that id and zip from dataset A and Zip_cd from dataset B.

Nonmmatching dataset:
if none of the zip from dataset A for an id match with dataset B.for instance 15123.

dataset A:

id zip
40228 93728
40228 93901
40228 94403
40228 95126
15123 30106

dataset B
id zip_cd
40228 95126
15123 30141
SAS Employee
Posts: 27

Re: Matching and Non-matching

Would this work?

[pre]
data A;
input id zip;
datalines;
40228 93728
40228 93901
40228 94403
40228 95126
15123 30106
;
run;

data B;
input id zip_cd;
datalines;
40228 95126
15123 30141
;
run;

/* find matching rows via data step merge */
proc sort data=a;
by id zip;
run;
proc sort data=b;
by id zip_cd;
run;

data matching;
merge a (in=a) b (rename=(zip_cd=zip) in=b);
by id zip;
if a and b;
run;

/* find non matching rows */
proc sql;
create table nonmatching as
select a.id, a.zip, b.id as b_id, b.zip_cd, sum(a.zip = b.zip_cd) as flag
from a, b
where a.id = b.id
group by a.id
having flag = 0;
quit;
run;

[/pre]
Super Contributor
Posts: 647

Re: Matching and Non-matching

Thanks.I worked.
Ask a Question
Discussion stats
  • 2 replies
  • 143 views
  • 0 likes
  • 2 in conversation