Count unique observations

[DME790]

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 ADM Murdock EMS ALE 2
Adelaide FT ADM Murdock EMS EMS 16
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

Any help or advice appreciated

Cheers

Dean

[ballardw · Accepted solution]

one way

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

[DME790]

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

[ballardw]

---

@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;

[Reeza]

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