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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.