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.
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
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
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
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
Yes, COUNT(SOMETHING) counts only non-missing values. But COUNT(*) counts every observation. GROUP BY treats missing values as a group. - PG
Thanks, Mike and PG, didn't know count(*) still counts even all of them are missing. One thing learned. Cheers!
Haikuo
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.