Solved
Contributor
Posts: 42

Need to find how many distict values along with count ?

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.

Accepted Solutions
Solution
‎07-03-2012 02:52 PM
Posts: 5,543

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

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.

PG

PG

All Replies
Solution
‎07-03-2012 02:52 PM
Posts: 5,543

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

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.

PG

PG
Posts: 3,167

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

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

Valued Guide
Posts: 765

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

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

12

Alice            1

Henry            1

Jane             1

Joyce            1

Judy             1

Robert           1

Ronald           1

Posts: 5,543

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

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

PG
Posts: 3,167

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

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

Haikuo

🔒 This topic is solved and locked.