topic Re: Selecting Unique Observations in SAS Programming

Selecting Unique Observations

TheKurgen — Mon, 23 Jul 2018 23:51:20 GMT

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

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.

Re: Selecting Unique Observations

novinosrin — Tue, 24 Jul 2018 00:30:25 GMT

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;

Re: Selecting Unique Observations

Patrick — Tue, 24 Jul 2018 00:35:04 GMT

@TheKurgen

I find the following documentation quite helpful:

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

And here an (untested) data step approach:

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

Re: Selecting Unique Observations

TheKurgen — Tue, 24 Jul 2018 00:59:29 GMT

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!

Re: Selecting Unique Observations

TheKurgen — Tue, 24 Jul 2018 00:59:53 GMT

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!

Re: Selecting Unique Observations

TheKurgen — Tue, 24 Jul 2018 01:02:29 GMT

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