Solved
Contributor
Posts: 28

# Summation by ID and by periods

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

Accepted Solutions
Solution
‎10-11-2013 08:03 AM
Posts: 4,736

## Re: Summation by ID and by periods

Posted in reply to loedrup_ab

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;

All Replies
Solution
‎10-11-2013 08:03 AM
Posts: 4,736

## Re: Summation by ID and by periods

Posted in reply to loedrup_ab

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;

Contributor
Posts: 28

## Re: Summation by ID and by periods

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 2 replies
• 236 views
• 0 likes
• 2 in conversation