topic How to get a distinct count of Y based on X in SAS Enterprise Guide

How to get a distinct count of Y based on X

FLCrime — Mon, 02 Mar 2020 14:45:13 GMT

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

Re: How to get a distinct count of Y based on X

PaigeMiller — Mon, 02 Mar 2020 15:01:07 GMT

Remove the duplicates, then count

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

Re: How to get a distinct count of Y based on X

novinosrin — Mon, 02 Mar 2020 15:39:22 GMT

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;

Re: How to get a distinct count of Y based on X

ballardw — Mon, 02 Mar 2020 16:44:34 GMT

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;

Re: How to get a distinct count of Y based on X

FLCrime — Mon, 02 Mar 2020 16:50:38 GMT

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.

Re: How to get a distinct count of Y based on X

PaigeMiller — Mon, 02 Mar 2020 18:05:02 GMT

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.

Re: How to get a distinct count of Y based on X

FLCrime — Mon, 02 Mar 2020 18:06:19 GMT

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.

Re: How to get a distinct count of Y based on X

PaigeMiller — Mon, 02 Mar 2020 18:10:37 GMT

Replace HAVE with COUNTYT.

The tables command under PROC FREQ should use variable name COUNTY

Re: How to get a distinct count of Y based on X

FLCrime — Mon, 02 Mar 2020 18:16:27 GMT

Not sure what I did wrong here.

Re: How to get a distinct count of Y based on X

PaigeMiller — Mon, 02 Mar 2020 18:47:22 GMT

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.

Re: How to get a distinct count of Y based on X

Ksharp — Tue, 03 Mar 2020 03:58:50 GMT

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