topic Select records with count of id > 1 in SAS Data Management

Select records with count of id > 1

CharlesC — Wed, 09 Apr 2014 23:51:32 GMT

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

Re: Select records with count of id > 1

Reeza — Wed, 09 Apr 2014 23:54:19 GMT

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

Re: Select records with count of id > 1

CharlesC — Thu, 10 Apr 2014 00:53:41 GMT

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

Re: Select records with count of id > 1

skillman — Thu, 10 Apr 2014 02:02:52 GMT

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