DATA Step, Macro, Functions and more

Count distinct values in proc summary/means

Reply
Contributor
Posts: 67

Count distinct values in proc summary/means

Hi I would be gratefull if anyone could tell me if is there posibility for counting distinct values in  summary or means procedure?

 

I know that I can do that by proc sql, however for cohesion of my code I would prefere use proc summary or means.

 

Thank you.

Super User
Posts: 10,588

Re: Count distinct values in proc summary/means

SUMMARY or MEANS alone don't do that, but a combination with PROC FREQ can do it:

proc freq data=sashelp.class noprint;
table sex / out=class;
run;

proc summary data=class n;
output out=want (drop=_type_);
run;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,935

Re: Count distinct values in proc summary/means

Yes and no.  SUMMARY/MEANS isn't built for that, so you have to jump through a hoop or two.  For example, suppose you want to know how many DISTINCT STATE values appear in your data set:

 

proc summary data=have nway;

class state;

var some_numeric_variable;

output out=stats (keep=state _freq_) n=;

run;

 

Now the number of observations in STATS is the number of STATE values that appeared in the original data set HAVE.  So it takes a little extra processing to capture that number, such as:

 

data _null_;

set stats nobs=n_states;

put n_states=;

stop;

run;

Super User
Posts: 10,588

Re: Count distinct values in proc summary/means

@Astounding's answer reveals a solution with two PROC SUMMARY steps:

proc summary data=sashelp.class nway;
class sex;
var age; * just some numeric variable for syntactical completeness;
output out=class (keep=sex _freq_) n=;
run;

proc summary data=class n;
output out=want (drop=_type_);
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 355

Re: Count distinct values in proc summary/means

You can use proc means and proc summary, if you have a numeric variable in your data set or create a numeric variable for that purpose.

 

Data A;
  Do ID = 'A', 'B', 'T', 'Z';
    Do NumVar = 1 To Ceil(Ranuni(1)*10);
	  Output;
	End;
  End;
Run;

Proc Means Data=A NoPrint;
  By ID;
  Var NumVar; 
  Output Out=Want (Keep=ID _FREQ_) N=; * remove _FREQ_ if not required;
Run;
Super User
Posts: 10,850

Re: Count distinct values in proc summary/means

[ Edited ]

PROC FREQ can do that.

 

ods select none;
ods output nlevels=want;
proc freq data=sashelp.class nlevels;
table _all_;
run;
ods select all;

proc print;run;
Contributor
Posts: 67

Re: Count distinct values in proc summary/means

[ Edited ]

I `m sorry I was not very clear, I need something like this

 

proc summary data=have missing;

class id1 id2 data;

var var1 var2 var3;

types id1*id2*id3;

output out=wan sum(var1)= max(var2) n(DISTINCT var3)=;/

run;

of course this way is not possible

 

 

I am able to do that, with two separate procedures and then merging result datasets, however, I was curious if it`s possible with one procedure or dataset just to make my code clearer

Super User
Posts: 13,941

Re: Count distinct values in proc summary/means

Provide a small example data set and what the result should be.

 

I am not even sure we are all using "distinct" in the same way.

Contributor
Posts: 67

Re: Count distinct values in proc summary/means

[ Edited ]

You can do what I want in proc sql:
proc sql;
create table want as
select id1, id2, date sum(var1), max(var2), count(distinct (var3)) from have;
quit;

I would like to know if there is option in proc summary/means for do the same.

Ask a Question
Discussion stats
  • 8 replies
  • 2690 views
  • 2 likes
  • 6 in conversation