Need to find how many distict values along with count ?

[sambasiva_ravuri_tcs_com]

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.

[PGStats · Accepted solution]

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

[Haikuo]

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

[MikeZdeb]

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

[PGStats]

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

[Haikuo]

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

Haikuo