topic Re: Use of the Count Distinct Function in SAS Programming

Use of the Count Distinct Function

Brickinnit — Wed, 30 Jul 2014 11:13:17 GMT

Hi All,

Hope all is well.

Sorry to bother you but have a quick question/need some advice. I have a dataset where I want to calculate the distinct counts for a particular variable - see below:

Variable 1 = UNSUBID Variable 2 = KITNUM

001001 4689

001001 4688

001001 4689

001002 5000

So, want to calculate the number of distinct counts by UNSUBID for variable KITNUM - so above, 001001 could have a distinct count of 2 for KITNUM (4689 and 4688), 001002 would have a distinct count of 1 for KITNUM (5000) etc.

I have looked on the forums and on SASHELP but cannot quite figure out how to do this (assume it's the count distinct that is required).

Again, thanks for looking and hope you can help 🙂

Re: Use of the Count Distinct Function

RW9 — Wed, 30 Jul 2014 12:04:45 GMT

Hi,

proc sql;

create table WANT as

select distinct

UNSUBJID,

COUNT(DISTINCT KITNUM) as CNT

from HAVE

group by UNSUBJID;

quit;

Re: Use of the Count Distinct Function

stat_sas — Wed, 30 Jul 2014 12:05:46 GMT

data have;
input UNSUBID KITNUM;
datalines;
001001 4689
001001 4689
001001 4688
001001 4689
001002 5000
001002 5000
001002 5000
001002 5000
;

proc sql;
select unsubid,count(distinct kitnum) as cnt from have
group by unsubid;
quit;

Re: Use of the Count Distinct Function

Brickinnit — Wed, 30 Jul 2014 12:24:34 GMT

Thanks All! Hopefully won't have to ask another dumb question in a while

Have a good day!