topic Re: Calculating daily average counts by group in SAS Programming

Calculating daily average counts by group

swioak — Tue, 26 Sep 2023 22:27:36 GMT

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

Re: Calculating daily average counts by group

mkeintz — Tue, 26 Sep 2023 22:51:22 GMT

@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?

Re: Calculating daily average counts by group

ballardw — Tue, 26 Sep 2023 22:52:41 GMT

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

Re: Calculating daily average counts by group

Ksharp — Wed, 27 Sep 2023 11:32:28 GMT

/*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;

Re: Calculating daily average counts by group

mkeintz — Thu, 28 Sep 2023 13:28:43 GMT

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;