DATA Step, Macro, Functions and more

Need to find how many distict values along with count ?

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

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
Respected Advisor
Posts: 4,930

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

Posted in reply to sambasiva_ravuri_tcs_com

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

View solution in original post


All Replies
Solution
‎07-03-2012 02:52 PM
Respected Advisor
Posts: 4,930

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

Posted in reply to sambasiva_ravuri_tcs_com

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
Respected Advisor
Posts: 3,156

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

Respected Advisor
Posts: 4,930

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
Respected Advisor
Posts: 3,156

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 281 views
  • 0 likes
  • 4 in conversation