BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CharlesC
Calcite | Level 5

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



1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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

CharlesC
Calcite | Level 5

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

skillman
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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