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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.