One to Many and Many to One transformations

A common occurrence: you have a file with candidates' marks, with the marks on the separate items as variables. You want to transform this to a file where each mark on an individual item constitutes a record. In other words, you want to go from:

cand item1 item2 item3 item4
1    4     5     6     7
2    2     0     3     4
3    1     4     5     8

to:

cand item mark
1    1    4
1    2    5
1    3    6
1    4    7
2    1    2
...

Here's how to do it:

1. data marks_by_item;
2.    set marks_by_candidate;
3.    array items item1-item4;
4.    do item = 1 to 4;
5.       mark = items[item];
6.       output;
7.    end;
8.    drop item1-item4;
9. run;

In line 3, you set up an array with the item scores. Using the do loop in line 4, we assign the item score to the new variable 'mark' and output that to file. Finally, in line 9 we drop the item variables.
Of course, you might want to go the other way:

 1. data marks_by_candidate_again;
 2.    set marks_by_item;
 3.    by cand;
 4.    array items item1-item4;
 5.    retain items;
 6.    if first.candid then
 7.       do i = 1 to 4;
 8.          items[i] = .;
 9.       end;
10.    items[item] = mark;
11.    if last.cand then output;
12.    drop item i mark;
13. run;

Line 3 specifies we are doing this by candidate, as we want to convert the four item scores of a candidate back to one record. In line 4, we create an array with the item scores. Notice that we are creating both the array and the four item variables here. Line 5 specifies that we want to retain the items array, otherwise it will be reset for each new record. In line 6 we are using the 'first.' system variable. This evaluates to 'true' for the first record in the 'by' group. We are using it to initialize the array. Then, for the last record in the 'by' group we output the data to file. Last, we delete the temporary variables.

A more concise way of doing the same (thanks NR), exploiting the 'loopiness' of SAS, is:

1. data marks_by_candidate_again (drop= item mark i); 
2.    array items item1-item4; 
3.    do i = 1 to 4; 
4.       set marks_by_item; 
5.       by cand; 
6.       items[i] =mark; 
7.       if last.cand then return; 
8.    end; 
9. run;