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.
Proc sql;
create table different as
select * from the biggerdataset
except
select * from the smallerdataset
;
quit;
may give you one place to start.
Have you tried proc compare?
Also post a small sample and your comparison needs please
I did. But it seems the observations are too big. It only listed the 1000.
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?
Yes.
/*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;
Proc sql;
create table different as
select * from the biggerdataset
except
select * from the smallerdataset
;
quit;
may give you one place to start.
Awesome, Ballardw! It works!
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?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.