LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 2009, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Mon, 17 Aug 2009 20:49:34 -0400
Reply-To:     Lou <lpogoda@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Lou <lpogoda@HOTMAIL.COM>
Organization: A noiseless patient Spider
Subject:      Re: Merge with duplicates
Comments: To: sas-l@uga.edu

"Ed A" <behrens900@gmail.com> wrote in message news:f3f0d1f9-35e6-44c8-a4b7-bac4dd20ae75@j9g2000vbp.googlegroups.com... > Hello, > > I am trying to do the following. > -Compare two data sets and flag every entry that occurs in BOTH > data sets. However, I only want to output the rows in the first data > set -- so if something is in the second data set and not in the first, > I don't want it. > > > data set1; > input name $ 1-16 num 17-20; > datalines; > Mark Williamson 111 > Joe Sanders 333 > John Doe 454 > > ; > data set2; > input name $ 1-16 num 17-20; > datalines; > Mark Williamson 787 > Mark Williamson 333 > John Doe 454 > Mike Lee 444 > Andrew Johnson 999 > ; > proc sort data = set1; > by name; > run; > proc sort data = set2; > by name; > run;

proc sort data = set1; by name num; proc sort data = set2; by name num; data set1only set2only both; merge set1 (in = set1) set2 (in = set2); by name num; if set1 and set2 then output both; if set1 and not set2 then output set1only; if set2 and not set2 then output set2only; run;

Print whichever output dataset you need.

> Data Matches; > merge set1(in=in_set1) set2(in=in_set2); > by name; > if in_set2; > if in_set1 = in_set2 then name_flag = 1; > else name_flag = 0; > if name = "" then name_flag = 0; > run; > proc sort data = set1; > by num; > run; > proc sort data = Matches; > by num; > run; > Data Matches2; > merge set1(in=in_set1) matches(in=in_matches); > by num; > if in_matches; > if in_set1 = in_matches then num_flag = 1; > else num_flag = 0; > if num=. then num_flag = 0; > total_flags = num_flag + name_flag; > run; > proc print data = matches2; > run; > > > > Ideal Output would be: > > > > > Obs name num name_flag > num_flag total_ flags > > 1 Mark Williamson 111 > 1 0 1 > 2 Joe Sanders 333 > 0 1 1 > 3 John Doe 454 > 1 1 2 > > > Actual Output is this: > -I don't want any output from data set 2 that is not in data set 1. > > > Obs name num name_flag > num_flag total_ flags > > 1 Mark Williamson 333 > 1 1 2 > 2 Mike Lee 444 > 0 0 0 > 3 John Doe 454 > 1 1 2 > 4 Mark Williamson 787 > 1 0 1 > 5 Andrew Johnson 999 > 0 0 0 > > > I tried nodups and nodupkeys but those did not work.


Back to: Top of message | Previous page | Main SAS-L page