SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Select records with count of id > 1

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Select records with count of id > 1

Hello,

  I have a dataset named victimsummary of unique crash ID's with dr_lic_num1 being the driver's license number.  Many dr_lic_num can show up in the dataset if they are involved in more than 1 crash.  I need to filter the dataset to output the records for dr_lic_num1's that occur more than n times.  At the beginning I am starting with n = 1 then working my way up after looking at the results.

I tried this code:

select *
from

(select ID, dr_lic_num1, count(dr_lic_num1) as freq1
    from victimsummary
    group by ID, dr_lic_num1
    )
where freq1 > 1;
quit;

Any help is greatly appreciated!\

-Charles




Accepted Solutions
Solution
‎04-09-2014 07:54 PM
Super User
Posts: 17,784

Re: Select records with count of id > 1

If the ID is unique per crash and you group by ID what do you expect the count to be?

View solution in original post


All Replies
Solution
‎04-09-2014 07:54 PM
Super User
Posts: 17,784

Re: Select records with count of id > 1

If the ID is unique per crash and you group by ID what do you expect the count to be?

Occasional Contributor
Posts: 14

Re: Select records with count of id > 1

Thank you... deleting ID made the query run.  Thanks again for your help.

SAS Employee
Posts: 85

Re: Select records with count of id > 1

This should also work for you: select dr_lic_num1, count(*) from victimsummary group by dr_lic_num1 having count(*) > 1

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 297 views
  • 0 likes
  • 3 in conversation