Count the number of individuals with the same characteristics

[WilliamB]

Hello,

I would like to use Proc SQL.

I have a SAS table:

An individual with the same characteristics is the same person

I would like to count the number of individuals with same characteristics, and get this table:

Name   C1 C2 C3 Count

Thomas 1 2 3          3
Thomas 3 4 3           1          
Thomas 1 2 5          1
Fabien 1 2 3          2
Fabien 7 8 9          1

An individual with not the same characteristics, is not the same person, so we have 3 thomas and 2 fabien.

But the first thomas was recorded 3times, and  the first fabien 2 times.

Thanks for your help

[stat_sas · Accepted solution]

Hi,

Something like this?

proc sql;

select *,count(*) as Count from have

group by name, c1,c2,c3;

quit;

[Reeza]

Try a basic PROC FREQ, TABULATE, MEANS/SUMMARY/UNIVARIATE. 

proc freq data=have noprint;

table name*c1*c2*c3 / list out=want;

run;

proc print data=want;
run;

---

@WilliamB wrote:

Hello,

I would like to use Proc SQL.

I have a SAS table:

An individual with the same characteristics is the same person

I would like to count the number of individuals with same characteristics, and get this table:

Name   C1 C2 C3 Count

Thomas 1 2 3          3
Thomas 3 4 3           1          
Thomas 1 2 5          1
Fabien 1 2 3          2
Fabien 7 8 9          1

An individual with not the same characteristics, is not the same person, so we have 3 thomas and 2 fabien.

But the first thomas was recorded 3times, and  the first fabien 2 times.

Thanks for your help

---

[novinosrin]

If your data is a representative sample of your real, @Reeza 's suggestion is prolly the best and neat. SQl will remerge. Reemerge is fancy word for a 2nd pass. However, it's up to you. A bit more fun on a sunny sunday afternoon from me:

data have;
input (Name   C1 C2 C3) ($);
datalines;
Thomas 1 2 3          3
Thomas 3 4 3           1
Thomas 1 2 5          1
Fabien 1 2 3          2
Fabien 7 8 9          1
Thomas 1 2 3          3
Thomas 1 2 5          1
;

data _null_;
  if _N_ = 1 then do;
  if 0 then set have;
     declare hash h( );
      h.defineKey('Name','C1','C2','C3');
      h.defineData('Name','C1','C2','C3','count');
      h.defineDone();
	 end;
set have end=last;
if h.check() ne 0 then do;count=0;count+1;h.add();end;
else if h.find()=0 then do;count+1;h.replace();end;
if last then h.output(dataset:'want');
run;