Help using Base SAS procedures

Getting an data

Reply
Frequent Contributor
Posts: 90

Getting an data

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.

Super User
Super User
Posts: 7,050

Getting an data

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;

Frequent Contributor
Posts: 81

Getting an data

*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;

Super User
Posts: 19,815

Getting an data

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.

Occasional Contributor
Posts: 16

Re: Getting an data

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

Super User
Posts: 10,035

Re: Getting an data

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

Ask a Question
Discussion stats
  • 5 replies
  • 220 views
  • 6 likes
  • 6 in conversation