topic Re: Count unique observations in SAS Enterprise Guide

Count unique observations

DME790 — Tue, 05 Dec 2017 22:59:09 GMT

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

Re: Count unique observations

ballardw — Tue, 05 Dec 2017 23:17:59 GMT

one way

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

Re: Count unique observations

Reeza — Tue, 05 Dec 2017 23:36:07 GMT

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

Re: Count unique observations

DME790 — Tue, 05 Dec 2017 23:52:02 GMT

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

Re: Count unique observations

ballardw — Wed, 06 Dec 2017 19:00:27 GMT

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