## Count unique observations

Solved
Frequent Contributor
Posts: 129

# Count unique observations

[ Edited ]

Hi All,

I need to count the number of unique LOGON_ID based on the FOOTPRINT. I'm not sure how to get the result I need.

Sample data below

``````Data test;
Input FOOTPRINT \$ EMPLOYMENT_TYPE \$ LOGON_ID \$ EMPLOYEE_NAME \$ CLUSTER \$	QUEUE_CD \$ num1;
datalines;
Adelaide FT AGA Andrews FAP CSA 6
Adelaide FT AGA Andrews FAP F04a 4
Adelaide FT AGA Andrews FAP F07 38
Sydney FT AGB Bev FAP F13 3
Sydney FT AGB Bev FAP F20 2
Sydney FT AGB Bev FAP F21 6
Sydney FT AGC Charlie FAP F22 53
Sydney FT AGC Charlie FAP F24a 2
Sydney FT AGD David FAP F25 1
Sydney FT AGE Edward FAP F60a 8
;
Run;``````

The result I'm looking from the example data is

 FOOTPRINT Unique_Count Adelaide 2 Sydney 4

Cheers

Dean

Accepted Solutions
Solution
‎12-05-2017 06:36 PM
Super User
Posts: 13,874

## Re: Count unique observations

one way

```proc sql;
select footprint, count(Logon_id) as unique_count
from (select distinct footprint, logon_id from test)
group by footprint
;
quit;```

All Replies
Solution
‎12-05-2017 06:36 PM
Super User
Posts: 13,874

## Re: Count unique observations

one way

```proc sql;
select footprint, count(Logon_id) as unique_count
from (select distinct footprint, logon_id from test)
group by footprint
;
quit;```
Frequent Contributor
Posts: 129

## Re: Count unique observations

Thanks @ballardw works a treat.

Is there a way you can output SQL to a new data set name like

output work.FootprintEMS;

Cheers

Dean

Super User
Posts: 13,874

## Re: Count unique observations

DME790 wrote:

Thanks @ballardw works a treat.

Is there a way you can output SQL to a new data set name like

output work.FootprintEMS;

Cheers

Dean

```proc sql;
create table want as
select footprint, count(Logon_id) as unique_count
from (select distinct footprint, logon_id from test)
group by footprint
;
quit;```
Super User
Posts: 23,932

## Re: Count unique observations

Two common methods:

COUNT DISTINCT in SQL

Double PROC FREQ

Fully worked examples here:

https://github.com/statgeek/SAS-Tutorials/blob/master/count_distinct_by_group.sas

☑ This topic is solved.