How to get a distinct count of Y based on X

[FLCrime]

Completely new to SAS Enterprise Guide. I am currently working on a table where I have "Offender" and "County" The offender can repeat but should only be counted as 1 even if it shows up 10 times. How can I count the occurance of county based on a unique Offender?

Offender          County

1                       Duval

1                       Duval

1                       Leon

2                       Hillsborough

4                       Duval

1                       Duval

5                       Clay

2                       Leon

This should result in:

Duval   2

Leon    2

Clay     1

Hillsborough   1

Manatee  1

[PaigeMiller]

Remove the duplicates, then count

/* REMOVE DUPLICATES */
proc sort data=have nodupkey;
    by county offender;
run;
/* COUNT */
proc freq data=have;
     tables county;
run;
    

[FLCrime]

This seems pretty straight forward to me. I tried and got the attached error. Again I apologize I have no experience with this program or SQL. I've been using the point and click features and building queries that way.

[PaigeMiller]

You must use the name of your SAS data set. You cannot use the name of the SAS data set that I used as an example, HAVE.

[FLCrime]

That was my assumption. I tried my file name which is "pROGRESS" yes terrible capitalization I know. I tried the what I think is the table name "COUNTYT" and the same error comes back saying that it doesn't exist.

[PaigeMiller]

Replace HAVE with COUNTYT.

The tables command under PROC FREQ should use variable name COUNTY

[FLCrime]

Not sure what I did wrong here.

[PaigeMiller]

I guess you are going to have to find the proper name of your SAS data set, because COUNTYT is not right. Perhaps it is SOMETHING.COUNTYT, where SOMETHING is a library name.

[novinosrin]

Hi @FLCrime  An alternative

data have;
input Offender          County :$15.;
cards;
1                       Duval
1                       Duval
1                       Leon
2                       Hillsborough
4                       Duval
1                       Duval
5                       Clay
2                       Leon
;



proc sql;
create table want as
select  county,count(*) as count
from (select distinct offender,county from have)
group by county;
quit;

[Ksharp · Accepted solution]

proc sql;
create table want as
select  county,count(distinct offender) as count
from  have
group by county;
quit;

[ballardw]

Another is a double Proc Freq:

proc freq data=have noprint;
   tables offender*county /output out=temp;
run;
proc freq data=temp;
   tables county;
run;