distinct count in data preparation

[Carrot17]

Hi,

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.

Best Regards,

Carrot17

[arodriguez]

Hi,

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;