Selecting Unique Observations

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Selecting Unique Observations

[ Edited ]

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.


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 2,041

Re: Selecting Unique Observations

[ Edited ]
Posted in reply to TheKurgen

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;

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Posts: 2,041

Re: Selecting Unique Observations

[ Edited ]
Posted in reply to TheKurgen

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;
New Contributor
Posts: 4

Re: Selecting Unique Observations

Posted in reply to novinosrin
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!
New Contributor
Posts: 4

Re: Selecting Unique Observations

Posted in reply to novinosrin
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!
Respected Advisor
Posts: 4,779

Re: Selecting Unique Observations

Posted in reply to TheKurgen

@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;
New Contributor
Posts: 4

Re: Selecting Unique Observations

Thanks Patrick, the link you provided was great and I will give the data step a shot
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 73 views
  • 0 likes
  • 3 in conversation