BookmarkSubscribeRSS Feed
Carrot17
Quartz | Level 8

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

1 REPLY 1
arodriguez
Lapis Lazuli | Level 10

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;

 

 

 

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

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

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1244 views
  • 0 likes
  • 2 in conversation