Create number counts based on different IDs and Classes?

[ybz12003]

Hello,

I'm looking to create a number count based on the different IDs and Dataset.  My sample dataset and my pursuing result are listed below. Thanks.

data datain;
      infile datalines delimiter='/';
  input ID : $10.  Class : 2.0;
datalines;
	THY/1/
	THY/1/
	THY/3/
	THY/3/
	THY/3/
	THY/3/
	THY/5/
	OUW/2/
	OUW/3/
	OUW/3/
	OUW/4/
	OUW/4/
	OUW/4/
	OUW/4/
	OUW/4/
;
run;

data dataout;
	infile datalines delimiter='/';
	input ID : $10.  Class : 2.0 Count : 2.0;
datalines;
	THY/1/2/
	THY/3/4/
	THY/5/1/
	OUW/2/1/
	OUW/3/2/
	OUW/4/5/
;
run;

[Reeza]

So basically just add the count of a ID/Class combo to each row?

This demonstrates the average but it's trivial to change it to count.
https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

[mkeintz]

If the data are already grouped by ID/CLASS, then:

data datain;
      infile datalines delimiter='/';
  input ID : $10.  Class : 2.0;
datalines;
	THY/1/
	THY/1/
	THY/3/
	THY/3/
	THY/3/
	THY/3/
	THY/5/
	OUW/2/
	OUW/3/
	OUW/3/
	OUW/4/
	OUW/4/
	OUW/4/
	OUW/4/
	OUW/4/
;
run;

data want;
  set datain;
  by id class notsorted;
  if last.class;
  n=coalesce(dif(_n_),_n_);
run;

The automatic variable _N_ is the iteration number of the data step, i.e. it counts the number of times the code in the data step is executed.

In this case, and in most cases, it is equivalent to the observation number.  So the "count" of an ID/CLASS group is just the value of _N_ at the end of a group minus the value of _N_ at the end of the preceding group (calcuated here by the dif function).  In the case of the first group the count is just _N_ itself.

[Kurt_Bremser · Accepted solution]

proc sql;
create table dataout as
  select
    id,
    class,
    count(*) as count
  from datain
  group by id, class
;
quit;