BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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
2 REPLIES 2
Daryl
SAS Employee
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]
SASPhile
Quartz | Level 8
Thanks.I worked.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 710 views
  • 0 likes
  • 2 in conversation