Desktop productivity for business analysts and programmers

how to seperate non matching records for 2 dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

how to seperate non matching records for 2 dataset

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?


Accepted Solutions
Solution
‎01-12-2012 12:51 PM
Occasional Contributor
Posts: 6

how to seperate non matching records for 2 dataset

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

View solution in original post


All Replies
Occasional Contributor
Posts: 6

how to seperate non matching records for 2 dataset

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

Solution
‎01-12-2012 12:51 PM
Occasional Contributor
Posts: 6

how to seperate non matching records for 2 dataset

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 174 views
  • 4 likes
  • 2 in conversation