Calculating daily average counts by group

[swioak]

Do I need to calculate the daily counts of a unique ID by group or can I do a frequency of group and divide by the number of days of my cohort?

Simple dat example:

ID     GROUP   DATE

1        AAA        1/1/23

2       AAA        1/1/23

3       AAA        1/10/23

4       BBB         1/1/23

5       BBB         1/2/23

5       BBB         1/3/23

Desired output:

GROUP        Average

AAA              0.3

BBB               1.0

[mkeintz]

---

@swioak wrote:

Do I need to calculate the daily counts of a unique ID by group or can I do a frequency of group and divide by the number of days of my cohort?

How can respondents tell you how to do something, if you don't state what the objective is?

[ballardw]

Here is a working data step to provide that data. You should provide your data in this form so we do not have to guess about things like variable types.

data have;
  input ID     GROUP $   DATE :mmddyy10.;
  format date mmddyy10.;
datalines;
1        AAA        1/1/23
2       AAA        1/1/23
3       AAA        1/10/23
4       BBB         1/1/23
5       BBB         1/2/23
5       BBB         1/3/23
;

Now, walk us through exactly what you do to arrive at those two "averages" especially for group AAA.

Be prepared to carry more decimals than you think you need.

---

@swioak wrote:

Do I need to calculate the daily counts of a unique ID by group or can I do a frequency of group and divide by the number of days of my cohort?

Simple dat example:

ID     GROUP   DATE

1        AAA        1/1/23

2       AAA        1/1/23

3       AAA        1/10/23

4       BBB         1/1/23

5       BBB         1/2/23

5       BBB         1/3/23

Desired output:

GROUP        Average

AAA              0.3

BBB               1.0

---

[Ksharp]

/*Assuming I understood what you mean.
And use the dataset @ballardw posted*/
data have;
  input ID     GROUP $   DATE :mmddyy10.;
  format date mmddyy10.;
datalines;
1        AAA        1/1/23
2       AAA        1/1/23
3       AAA        1/10/23
4       BBB         1/1/23
5       BBB         1/2/23
5       BBB         1/3/23
;
proc sql;
create table want as
select group,count(*)/(range(date)+1) as Avergae
 from have 
  group by group;
quit;

[mkeintz]

A data step solution would look like:

data have;
  input ID     GROUP $   DATE :mmddyy10.;
  format date mmddyy10.;
datalines;
1        AAA        1/1/23
2       AAA        1/1/23
3       AAA        1/10/23
4       BBB         1/1/23
5       BBB         1/2/23
5       BBB         1/3/23
;

data want (keep=id average);
  set have ;
  by group;

  date_range+dif(date);
  if first.group then call missing(date_range,n_ids);
  n_ids+1;
  if last.group;
  average=n_ids/(date_range+1);
run;