BookmarkSubscribeRSS Feed
raveena
Obsidian | Level 7

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.

5 REPLIES 5
Tom
Super User Tom
Super User

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
Quartz | Level 8

*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
Super User

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
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 956 views
  • 6 likes
  • 6 in conversation