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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 26390 views
  • 5 likes
  • 5 in conversation