Select records with count of id > 1

[CharlesC]

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

[Reeza · Accepted solution]

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

[CharlesC]

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

[skillman]

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