topic Create number counts based on different IDs and Classes? in SAS Programming

Create number counts based on different IDs and Classes?

ybz12003 — Mon, 21 Nov 2022 23:51:05 GMT

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;

Re: Create number counts based on different IDs and Classes?

Reeza — Mon, 21 Nov 2022 23:53:00 GMT

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

Re: Create number counts based on different IDs and Classes?

mkeintz — Tue, 22 Nov 2022 00:31:48 GMT

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.

Re: Create number counts based on different IDs and Classes?

Kurt_Bremser — Tue, 22 Nov 2022 07:37:04 GMT

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