Below is some data:
Test Day1 Day2 Score
A 1 2 100
B 1 3 62
C 3 4 90
D 2 4 20
E 4 5 80
I am trying to take the values from column 'day' and 'day2' and use them to select the row number for the column score. For example for Test A I would like to find the sum of 100 and 62 because that is the values of the first and second rows of score. Test B I would like to find the sum of 100, 62 and 90. Does anyone have any ideas on how to go about doing this? I am looking to use something similar to the indirect function in Excel? Thank You
The trick is to convert variable "Score" as a row. Could not think of an easy way how to avoid SAVE/GET - room for improvements.
file handle tmp
/name = "C:\DATA\Temp".
***.
data list free /Test (a1) Day1 (f8) Day2 (f8) Score (f8).
begin data
A 1 2 100
B 1 3 62
C 3 4 90
D 2 4 20
E 4 5 80
end data.
comp f = 1.
var wid all (12).
save out "tmp\data.sav".
***.
get "tmp\data.sav"
/keep score.
flip.
comp f = 1.
match files
/file "tmp\data.sav"
/table *
/by f
/drop case_lbl.
comp stat = 0.
do rep var = var001 to var005
/k = 1 to 5.
if range(k, Day1, Day2) stat = sum(stat, var).
end rep.
list Test Day1 Day2 Score stat.
The result:
Test Day1 Day2 Score stat
A 1 2 100 162
B 1 3 62 252
C 3 4 90 110
D 2 4 20 172
E 4 5 80 100
Number of cases read: 5 Number of cases listed: 5