Hi Guys,
I currently have 2 sets of data: A and B,
and within each data set, there are 2 variables: Date, Key.
Basically, what I am trying to do is to identify the observations that are FOUND in B and NOT FOUND in A on a certain date with a certain key and then extract them into another set of data.
So for eg:
Set A Set B
Date Key Date
20080330 1 20080330 1
20080330 2 20080330 2
20080330 3 20080330 4
20080401 1 20080401 1
20080401 2 20080401 2
20080401 4 20080401 3
So in this case, Set C (which is the set I wish to create) will have the following observations:
Date Key
20080330 4
20080401 3
The important thing to note that Dataset C should contain only observations that are FOUND in B but NOT FOUND in A. (Not the other way round or both ways round).
Many thanks guys!
Made to order for the little-used "except" set operator:
proc sql;
create table c as
select * from b except select * from a;
quit;
Karl
Hi!
It sounds like a Proc Sql join.
For example:
proc sql;
create table c as
select b.date,b.key
from a right join b
on a.date=b.date and
a.key=b.key
where a.date=. and a.key=.;
quit;
After a sort, a merge with in= option should give what you are looking for.
proc sort data=dataset_a;
by date key;
run;
proc sort data=dataset_b;
by date key;
run;
data dataset_c;
merge dataset_a (in=aa) dataset_b(in=bb);
by date key;
if bb and not aa;
run;
There is probably a proc sql approach that is better, but this is one that has worked across many versions of SAS.
Steve Denham
Made to order for the little-used "except" set operator:
proc sql;
create table c as
select * from b except select * from a;
quit;
Karl
Without doubt, the best solution is Proc SQL with the EXCEPT set operator.
For a DATA step solution without the need for sorting, here is a simple hash solution (using the plural forms of the variable names):
data setc;
declare hash a(dataset:"seta",ordered:"a");
a.defineKey("dates","keys");
a.defineDone();
do until(done);
set setb end=done;
if a.check() ne 0 then output;
end;
stop;
run;
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.