topic Re: Getting an data in SAS Procedures

Getting an data

raveena — Mon, 19 Sep 2011 16:23:48 GMT

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

Please let me know how to get this desired output.

Thanks in Advance.

Getting an data

Tom — Mon, 19 Sep 2011 17:16:12 GMT

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;

Getting an data

NickR — Mon, 19 Sep 2011 17:41:30 GMT

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

Getting an data

Reeza — Mon, 19 Sep 2011 17:45:32 GMT

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.

Re: Getting an data

Hobbes — Mon, 19 Sep 2011 17:47:40 GMT

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

Re: Getting an data

Ksharp — Wed, 21 Sep 2011 05:16:52 GMT

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