05-19-2016 05:24 AM - edited 05-19-2016 05:26 AM
I would like to seek your help regarding distinct count in Data Preparation. Our requirements is to get the distinct count of employee id per year however the employee name is not unique but we need to display the employee name in the info window link as drill-through but in the main graph we should get the distinct employee count.
I tried below expression in data preparation no error but got incorrect result
expression: distinct(employee id)
aggregation: group by employee id, group by year, Employee Name has blank aggregation.
Please see below sample data. In below data the correct result should be 2013 = 2 employee id, 2014 = 3 but in my result which is wrong 2013=4 and 2014=4. Please help. Thank you.
05-20-2016 10:51 AM - edited 05-20-2016 10:52 AM
I think that the problem is your group sentence, since you are grouping by employee id and name, is counting diferents pairs of id and names. Since your names and id's don't have bijection, there are four observations on each year.
If you like to count group by employee id use the following code
proc sql; create table results as( select count(distinct employee_id) as count, Year from _data_ group by year ); quit;