Summation by ID and by periods

[loedrup_ab]

Dear anyone

I have a dataset. Respondent have ID numbers. They are rated by two different scores (score 1 and 2) each week for 13 weeks. These 13 weeks are categorized into four periods. Period 0 (week 1), period 1 (week 2 – 5), period 3 (week 6 – 9) and period 4 (week 10 – 13). See table 1 - please note the missing values.

I want to summarize scores for each period for each ID. And I want a mean score for each period. See table 2 (made by excel).

I cannot find this explained elsewhere in here. Can anyone help? I am using SAS 9.3

Sincerely

Anders Lødru

Table1

ID	Week	Period	Score_1	Score_2
11	1	0	1.73	1.67
11	2	1
11	3	1	2.00	3.33
11	4	1	2.73	3.33
11	5	1
11	6	2	2.27	3.00
11	7	2	2.40	2.00
11	8	2
11	9	2	1.93	2.67
11	10	3	2.53	3.00
11	11	3
11	12	3	2.13	3.33
11	13	3	1.67	1.33
12	1	0	2.53	2.00
12	2	1	2.20	1.67
12	3	1	2.60	2.33
12	4	1	2.67	2.00
12	5	1	1.53	1.33
12	6	2	1.40	1.00
12	7	2	1.00	1.00
12	8	2	1.80	1.00
12	9	2	1.67	1.00
12	10	3	1.00	1.00
12	11	3	1.93	2.33
12	12	3	1.93	2.33
12	13	3	1.80	2.33

Table 2

ID	Week	Period	Score_1	Score_2	Cummulative_score_1_per_period	Cummulative_score_2_per_period	Mean_Cum_score_1_per_period	Mean_Cum_score_2_per_period
11	1	0	1.73	1.67	1.73	1.67	1.73	1.67
11	2	1
11	3	1	2.00	3.33
11	4	1	2.73	3.33
11	5	1			4.73	6.67	2.37	3.33
11	6	2	2.27	3.00
11	7	2	2.40	2.00
11	8	2
11	9	2	1.93	2.67	6.60	7.67	2.20	2.56
11	10	3	2.53	3.00
11	11	3
11	12	3	2.13	3.33
11	13	3	1.67	1.33	6.33	7.67	2.11	2.56
12	1	0	2.53	2.00	2.53	2.00	2.53	2.00
12	2	1	2.20	1.67
12	3	1	2.60	2.33
12	4	1	2.67	2.00
12	5	1	1.53	1.33	9.00	7.33	2.25	1.83
12	6	2	1.40	1.00
12	7	2	1.00	1.00
12	8	2	1.80	1.00
12	9	2	1.67	1.00	5.87	4.00	1.47	1.00
12	10	3	1.00	1.00
12	11	3	1.93	2.33
12	12	3	1.93	2.33
12	13	3	1.80	2.33	6.67	8.00	1.67	2.00

[Patrick · Accepted solution]

Your "table 2" looks more like a report than a table. Below code will give you a table so sums and averages are populated in all rows and not only the last one.

For a report use Proc Report or Proc Tabulate. But even there statistics per group will need to appear on an additional line for sub-totals and not simply on the last line of data per group.

proc format;
  value period
    1     = 0
    2-5   = 1
    6-9   = 2
    10-13 = 3
  ;
run;

data have;
  infile datalines truncover;
  input ID Week Period Score_1 Score_2;
  datalines;
11 1 0 1.73 1.67
11 2 1
11 3 1 2.00 3.33
11 4 1 2.73 3.33
11 5 1
11 6 2 2.27 3.00
11 7 2 2.40 2.00
11 8 2
11 9 2 1.93 2.67
11 10 3 2.53 3.00
11 11 3
11 12 3 2.13 3.33
11 13 3 1.67 1.33
12 1 0 2.53 2.00
12 2 1 2.20 1.67
12 3 1 2.60 2.33
12 4 1 2.67 2.00
12 5 1 1.53 1.33
12 6 2 1.40 1.00
12 7 2 1.00 1.00
12 8 2 1.80 1.00
12 9 2 1.67 1.00
12 10 3 1.00 1.00
12 11 3 1.93 2.33
12 12 3 1.93 2.33
12 13 3 1.80 2.33
;
run;

proc sql;
  create table want as
    select
      ID,
      Week,
      put(week,period.) as Period,
      Score_1,
      Score_2,
      sum(Score_1) as Cummulative_score_1_per_period,
      sum(Score_2) as Cummulative_score_2_per_period,
      avg(Score_1) as Mean_Cum_score_1_per_period format=16.2,
      avg(Score_2) as Mean_Cum_score_2_per_period format=16.2
    from have
    group by id, calculated period
    order by id, week
    ;
quit;

[loedrup_ab]

Dear Patrick

Thank you for your helpfull answer. I am sorry for my delayed reply. It took me a while to get the code to work on my dataset, but now it is working just fine. Thank you again.

Sincerely

Anders