Desktop productivity for business analysts and programmers

Count unique observations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 129
Accepted Solution

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

FOOTPRINTUnique_Count
Adelaide2
Sydney4

 

Any help or advice appreciated

 

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;

View solution in original post


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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 419 views
  • 1 like
  • 3 in conversation