Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

distinct count in data preparation

Reply
Contributor
Posts: 54

distinct count in data preparation

[ Edited ]

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.

 

distinct count.JPG

 

Best Regards,

Carrot17

Frequent Contributor
Posts: 144

Re: distinct count in data preparation

[ Edited ]

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;

 

 

 

Ask a Question
Discussion stats
  • 1 reply
  • 264 views
  • 0 likes
  • 2 in conversation