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
If the ID is unique per crash and you group by ID what do you expect the count to be?
If the ID is unique per crash and you group by ID what do you expect the count to be?
Thank you... deleting ID made the query run. Thanks again for your help.
This should also work for you: select dr_lic_num1, count(*) from victimsummary group by dr_lic_num1 having count(*) > 1
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.