Help using Base SAS procedures

Summation by ID and by periods

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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

IDWeekPeriodScore_1Score_2
11101.731.67
1121
11312.003.33
11412.733.33
1151
11622.273.00
11722.402.00
1182
11921.932.67
111032.533.00
11113
111232.133.33
111331.671.33
12102.532.00
12212.201.67
12312.602.33
12412.672.00
12511.531.33
12621.401.00
12721.001.00
12821.801.00
12921.671.00
121031.001.00
121131.932.33
121231.932.33
121331.802.33

Table 2

IDWeekPeriodScore_1Score_2Cummulative_score_1_per_periodCummulative_score_2_per_periodMean_Cum_score_1_per_periodMean_Cum_score_2_per_period
11101.731.671.731.671.731.67
1121
11312.003.33
11412.733.33
11514.736.672.373.33
11622.273.00
11722.402.00
1182
11921.932.676.607.672.202.56
111032.533.00
11113
111232.133.33
111331.671.336.337.672.112.56
12102.532.002.532.002.532.00
12212.201.67
12312.602.33
12412.672.00
12511.531.339.007.332.251.83
12621.401.00
12721.001.00
12821.801.00
12921.671.005.874.001.471.00
121031.001.00
121131.932.33
121231.932.33
121331.802.336.678.001.672.00

Accepted Solutions
Solution
‎10-11-2013 08:03 AM
Respected Advisor
Posts: 3,887

Re: Summation by ID and by periods

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;

View solution in original post


All Replies
Solution
‎10-11-2013 08:03 AM
Respected Advisor
Posts: 3,887

Re: Summation by ID and by periods

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.

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

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