topic Need to find how many distict values along with count ? in SAS Programming

Need to find how many distict values along with count ?

sambasiva_ravuri_tcs_com — Tue, 03 Jul 2012 18:32:06 GMT

Dear All,

We can get the count of distinct values using PROC SQL.

Apart from that, I need to display the Distinct values along with count of each distinct value like below including spaces.

Distinct Values count

6 25000

9 40000

AS 20000

' ' 500000

000 2000000

Note: ' ' - denotes spaces .

Regards,

S Ravuri.

Re: Need to find how many distict values along with count ?

PGStats — Tue, 03 Jul 2012 18:52:07 GMT

If I understand your question correctly, to display the count for each distinct value use:

proc sql;

select value, count(*) as n

from myData

group by value;

Note : keyword DISTINCT is not required, it is implied by the GROUP BY clause.

Re: Need to find how many distict values along with count ?

Haikuo — Tue, 03 Jul 2012 19:07:28 GMT

Hi PG,

count() only counts non-missing items, please see the following:

data have;

set sashelp.class sashelp.class(drop=_all_ obs=5);

run;

/*count()*/

proc sql;

select age, count(age) as ct from have group by age;quit;

/*sum()*/

proc sql;

select age, sum(age>=.) as ct from have group by age;quit;

OP, besides PG's sql approach, proc freq seems handy as well:

data have;

set sashelp.class sashelp.class(drop=_all_ obs=5);

run;

proc sort data=have;

by age;

run;

/*proc freq*/

proc freq data=have ;

tables age /all;

by age;

output out=want2 n nmiss;

run;

proc print;run;

Regards,

Haikuo

Re: Need to find how many distict values along with count ?

MikeZdeb — Tue, 03 Jul 2012 19:39:52 GMT

hi ... counting missing values ...

data have;

set sashelp.class (keep=age name);

if ranuni(999) gt .7 then call missing(age);

if ranuni(999) gt .2 then call missing(name);

run;

proc sql;

select age, count(*) as n

from have

group by age;

select name, count(*) as n

from have

group by name;

quit;

Age n

. 6

12 4

13 1

14 3

15 4

16 1

Name n

Alice 1

Henry 1

Jane 1

Joyce 1

Judy 1

Robert 1

Ronald 1

Re: Need to find how many distict values along with count ?

PGStats — Tue, 03 Jul 2012 20:02:11 GMT

Yes, COUNT(SOMETHING) counts only non-missing values. But COUNT(*) counts every observation. GROUP BY treats missing values as a group. - PG

Re: Need to find how many distict values along with count ?

Haikuo — Tue, 03 Jul 2012 20:11:48 GMT

Thanks, Mike and PG, didn't know count(*) still counts even all of them are missing. One thing learned. Cheers!

Haikuo