topic how to seperate non matching records for 2 dataset in SAS Enterprise Guide

how to seperate non matching records for 2 dataset

mrug12 — Thu, 12 Jan 2012 17:20:41 GMT

Hi I have 2 dataset for example

Table 1

a 1

b 2

c 3

Table 2

a 1

b 1

c 1

a 2

b 2

c 2

a 3

b 3

c 3

how can I seperate non matching records from table-2?

how to seperate non matching records for 2 dataset

2much2learn — Thu, 12 Jan 2012 17:44:22 GMT

I am not 100% sure about what exactly OP wanted, but here to start the discussion:

data t1;

infile cards;

input id $ a;

cards;

a 1

b 2

c 3

;

data t2;

infile cards;

input id $ a;

cards;

a 1

b 1

c 1

a 2

b 2

c 2

a 3

b 3

c 3

;

proc sort data=t1;

by id a;

run;

proc sort data=t2;

by id a;

run;

data m nm;

merge t1 (in=t1) t2(in=t2);

by id a;

if t1 and t2 then output m;

else if t2 and not t1 then output nm;

run;

'm' will be the records shared by two tables, nm will be the records uniquely exist in table2.

Kindly Regards,

Haikuo

how to seperate non matching records for 2 dataset

2much2learn — Thu, 12 Jan 2012 17:51:53 GMT

And if you only care about non-matching records, SQL is more handy as it does not require sorting:

proc sql;

create table nm as

select * from t2

except

select * from t1;

quit;

Haikuo