Selecting Unique Observations

[TheKurgen]

Hi All,

I have the following two data sets RETITLE and MATCH (sample)

retitle				match
ID	date	time		ID	date	time
1	18Jun2018	13:25:21		1	18Jun2018	13:25:21
2	28Jun2018	11:33:08		2	28Jun2018	11:33:08
4	29Jun2018	11:24:33		3	29Jun2018	14:13:40
6	29Jun2018	13:25:45		4	29Jun2018	11:24:33
8	21Jun2018	9:59:05		5	29Jun2018	12:05:34
				6	29Jun2018	13:25:45
				7	14Jun2018	8:25:34
				8	23Jun2018	9.22.00

I am trying to create a table from these two data sets where the combination of ID, Date and Time are unique to RETITLE. In the above sample there would only be the one observation

8

21Jun2018

9:59:05

I was playing around with SQL but got stuck when considering the multiple variables.. I had the following code for a single variable but wanted to expand it to include date and time;

proc sql;

create table anysingles as

select *

from retitle

where id not in (select id from match);

quit;

any help would be great.

[novinosrin · Accepted solution]

Exactly 3 variables in both datasets with the same names?

if yes, 

proc sql;
create table want as
   select * from retitle
   except
   select * from match;
quit;

[TheKurgen]

Thanks novinosrin, there are other variables in both data sets, but I think If I limit my data sets to these three varaibles this does the trick!

[TheKurgen]

Thanks novinosrin, there are other variables in both data sets, but I think If I limit my data sets to these three variables this does the trick!

[Patrick]

@TheKurgen

I find the following documentation quite helpful:

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n0vo2lglyrnexwn14e...

And here an (untested) data step approach:

data want2;
  merge retitle match(in=inmatch);
  by id date time;
  if not inmatch;
run;

[TheKurgen]

Thanks Patrick, the link you provided was great and I will give the data step a shot