Hi SAS community,
I have two different datasets: 'base' and 'new'. Many of the observations within the two datasets are the same, but a few are not. I would like to identify which observations from 'new' are not in 'base' so that I can create a new dataset 'want' that includes all in 'base' and then the additions from 'new'.
Thanks!
Assuming that the column names are same in both the tables.
proc sql;
create table temp as
select * from new
except
select * from base;
quit;
Assuming that the column names are same in both the tables.
proc sql;
create table temp as
select * from new
except
select * from base;
quit;
With an example
data base;
input ID name$;
cards;
1 Test
2 Yes
3 Push
4 In
5 Ye
;
run;
data new;
input ID name$;
cards;
1 Test
2 Yes
4 In
5 Ye
;
run;
proc sql;
select * from base
except all
select * from new;
quit;
Output:
ID name
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
3 Push
On top of Hima's suggestion, further down the road to your goal, create new data set based on your base plus all of obs not base but in new. You can also try to update your base, but that can't be done in one SQL pass:
data base;
input ID name$;
cards;
1 Test
2 Yes
3 Push
4 In
5 Ye
;
run;
data new;
input ID name$;
cards;
1 Test
2 Yes
4 In
5 Ye
6 sfd
;
proc sql;
create table base_new as
select * from new
union corr
select * from base;
quit;
proc print;run;
Haikuo
Actually new Hash() method in 9.2 makes this job very much native:
data _null_;
if _n_=1 then do;
if 0 then set base;
dcl hash h(dataset:'base', multidata:'y');
h.definekey(all:'y');
h.definedata(all:'y');
h.definedone();
end;
set new end=done;
rc=h.ref();
if done then rc=h.output(dataset:'want');
run;
Regards,
Haikuo
Thanks Haikuo for your help. The last code does work for when I'm combining the unique observations from base and new. What amendments do you suggest I do if I simply want to delete the duplicate observations from the new dataset? (I.e. the duplicates would be those that already exist in dataset base).
Thanks.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.