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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.