BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DME790
Pyrite | Level 9

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
ballardw
Super User

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
Pyrite | Level 9

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

@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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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