BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

Hello:

 

I have table A with 32 variables of 4286 observations and table B with 32 variables of 3996 observations.   All the variables of the two tables are the same.  How to find out the difference of 290 observations between A and B.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Proc sql;

   create table different as

   select * from the biggerdataset

   except

   select * from the smallerdataset

   ;

quit;

 

may give you one place to start.

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20

Have you tried proc compare?

Also post a small sample and your comparison needs please

ybz12003
Rhodochrosite | Level 12

I did.  But it seems the observations are too big.   It only listed the 1000. 

novinosrin
Tourmaline | Level 20

is it something like 

 

pkey var1 var2 var3

a        1      2      3

b        4      5      6

c         7      8      9

 

pkey var1 var2 var3

a        1      2      3

b        4      5      6

 

Pick the C?

 

 

 

 

novinosrin
Tourmaline | Level 20
/*Really like that example?*/

data campare;
    if _N_ = 1 then do;
    if 0 then set tableB;/*smaller dataset*/
    declare hash h(dataset: 'tableB');/*smaller dataset*/
    h.defineKey('pkey');
    h.defineData(all:'y');
    h.defineDone(); 
  end;
  set tableA; /*the larger dataset*/
  if h.check() ne 0 then output;
  run;
ballardw
Super User

Proc sql;

   create table different as

   select * from the biggerdataset

   except

   select * from the smallerdataset

   ;

quit;

 

may give you one place to start.

ybz12003
Rhodochrosite | Level 12

Awesome, Ballardw!  It works!

henrykira
Calcite | Level 5

So what if it is something like:

 

 

pkey var1 var2 var3

a        1      2      3

b        4      5      5

 

 

pkey var1 var2 var3

a        1      2      3

b        4      5      6

 

pick b and display both?

bijay
Calcite | Level 5

As mentioned in the above solution, use except with proc sql, and add union to concatenate the outcome.

 

data test1;
input pkey $ var1 var2 var3;
datalines;
a 1 2 3
b 4 5 5
;
run;

data test2;
input pkey $ var1 var2 var3;
datalines;
a 1 2 3
b 4 5 6
;
run;

proc sql;
(select * from test1
except
select * from test2 )
union
(select * from test2
except
select * from test1)
;
quit;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 29683 views
  • 6 likes
  • 5 in conversation