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 |
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;
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.