Getting an data

[raveena]

Hi,

I have 2 tables member and memberdemohistory, need to select the records that are different from the member table.Member table will have unique memno id.But in memberdemohistory , the members can have multiple records.so i need to compare with the member table record.

For example,

Member Table

Memno  Firstname   Lastname   address1             address2    city            county           zipcode

12345      Kimberly      cowan       32 Moore ave                            Nassau            NY               11556

Demohistory

Memno  Firstname   Lastname    address1                  address2         city            county           zipcode

12345      Kimberly      cowan       130 Jerusalem st      apt #415          Nassau            NY               11556

12345      Kimberly      cowan       32 Moore ave                                       Nassau            NY               11556

12345      Kimberly      cowan       130 Jerusalem st      apt #415          Nassau            NY               11556

12345      Kimberly      cowan       130 Jerusalem st      apt #415           Nassau            NY               11556

12345      Kimberly      cowan       32 Moore ave                                        Nassau            NY               11556

Output should be :

Memno  Firstname   Lastname    address1                  address2         city            county           zipcode

12345      Kimberly      cowan       130 Jerusalem st      apt #415          Nassau            NY               11556

12345      Kimberly      cowan       130 Jerusalem st      apt #415          Nassau            NY               11556

12345      Kimberly      cowan       130 Jerusalem st      apt #415           Nassau            NY               11556

Please let me know how to get this desired output.

Thanks in Advance.

[Tom]

One way is to merge the two by MEMNO and compare the values. Because the variables have the same name in both dataset you will need to rename one set of names so that you can have two different variable names to compare.

One way to avoid having to type all those new names is to create a dataset that looks like the history file, but has the address information from the master file.  Then you could use PROC COMPARE to compare the two.

data check ;

   merge demo demohistory(keep=memno) ;

  by memno;

run;

proc compare data=check compare=demohistory ;

  id memno;

run;

[NickR]

*Create sample Member dataset;

data Member;

          length Memno 8 Firstname Lastname address1 address2 city county zipcode $100;

          infile datalines dlm=',';

          input Memno Firstname Lastname address1 address2 city county zipcode;

datalines;

12345,Kimberly,cowan,32 Moore ave, ,Nassau,NY,11556

;

run;

*Create sample Demohistory dataset;

data Demohistory;

          length Memno 8 Firstname Lastname address1 address2 city county zipcode $100;

          infile datalines dlm=',';

          input Memno Firstname Lastname address1 address2 city county zipcode;

datalines;

12345,Kimberly,cowan,130 Jerusalem st,apt #415,Nassau,NY,11556

12345,Kimberly,cowan,32 Moore ave, ,Nassau,NY,11556

12345,Kimberly,cowan,130 Jerusalem st,apt #415,Nassau,NY,11556

12345,Kimberly,cowan,130 Jerusalem st,apt #415,Nassau,NY,11556

12345,Kimberly,cowan,32 Moore ave, ,Nassau,NY,11556

;

run;

*Sort the data before merging;

proc sort data=member; by memno firstname lastname address1 address2 city county zipcode; run;

proc sort data=Demohistory; by memno firstname lastname address1 address2 city county zipcode; run;

data output;

          merge member(in=a) demohistory(in=b);

          by memno firstname lastname address1 address2 city county zipcode;

          if b and not a; *Output records present in Demohistory which are not present in Member dataset;

run;

proc print; run;

[Reeza]

I think NickR solution will work if you drop address2 in the by statement and sort statements, but then you might have diff apt numbers. But your examples also don't cover this scenario.

You can hardcode the comparison in for each variable  but I also think you might need to provide more info, I'm assuming that you you're looking to add in the apt number but not sure.

A little more explanation of the logic would help.

[Hobbes]

Youmay try this:

PROC SORT DATA=MEMBER OUT=MEMBER2;

      BY Memno Firstname Lastname address1 address2 citycounty zipcode;

RUN;

PROC SORT DATA=Demohistory OUT=DHIST2;

      BY Memno Firstname Lastname address1 address2 citycounty zipcode;

RUN;

DATA RESULT;

      MERGE MEMBER2(IN=A) DHIST2(IN=B);

   BY Memno Firstname Lastname address1 address2 citycounty zipcode;

      IF B AND NOT A;

RUN;

/hobbes

[Ksharp]

I think that  For your situation , the best way is to SQL.

data member;
input (a1-a8) (& $20.);
cards;
12345      Kimberly      cowan       32 Moore ave            .                Nassau            NY               11556
;
run;
data demo;
input (a1-a8) (& $20.);
cards;
12345      Kimberly      cowan       130 Jerusalem st      apt #415          Nassau            NY               11556
12345      Kimberly      cowan       32 Moore ave             .                          Nassau            NY               11556
12345      Kimberly      cowan       130 Jerusalem st      apt #415          Nassau            NY               11556
12345      Kimberly      cowan       130 Jerusalem st      apt #415           Nassau            NY               11556
12345      Kimberly      cowan       32 Moore ave              .                          Nassau            NY               11556
;
run;
proc sql noprint;
 create table want as 
 select * from
  demo natural join
  (select * from demo except select * from member );
quit;

Ksharp