Restructure from tall to wide (general solution)

 

Download as .sps Shows as plain text

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
*(Q) The data are in a "stacked" format according to time point like the following:

	ID  TIME  VAR1  VAR2  VAR3
	1   1     A     D     G   
	1   2     B     E     H
	1   3     C     F     I
	[...]

*	I'd like to re-arrange the data so that there is a single row for each 
	id, creating columns for each variable (VAR1-VAR3) at each time point:

*	ID  VAR11  VAR12  VAR13  VAR21  VAR22  VAR23  VAR31  VAR32  VAR33 
	1   A      B      C      D      E      F      G      H      I
	2   ...

*	One thing to note is that there are more than 3 time points and more than 3 variables.
*	Any and all suggestions appreciated.

* (A) Posted to spss newsgroup by rlevesque@videotron.ca on 2001/10/10.
* SPSS web site ----> http://pages.infinit.net/rlevesqu/index.htm

* Note naming the original variables var1, var2 and var3 does not work because if time =12 (say) then 
	there wil confusion between 
	var1 at time 11 which is var111 and
	var11 at time 1 which is var111.


DATA LIST LIST /ID(F8.0)  time1(F8.0)  var1t(A8)  var2t(A8)  var3t(A8).
BEGIN DATA
1 1 A E	I
1 2 B F J
1 3 C G K
1 4 D H L
2 1 AA EE II
2 2 BB FF JJ
2 3 CC GG KK
2 4 DD HH LL
END DATA.
LIST.

SET MPRINT=no.
*//////////////////////////////////.
DEFINE !transf(nbvars=!TOKENS(1) /nbtime=!TOKENS(1))

!DO !cnt=1 !TO !nbvars.
STRING !CONCAT('var',!cnt,'t1') TO  !CONCAT('var',!cnt,'t',!nbtime)(A8).
VECTOR !CONCAT('var',!cnt)=!CONCAT('var',!cnt,'t1') TO !CONCAT('var',!cnt,'t',!nbtime).
!DOEND
VECTOR v=var1t1 TO !CONCAT('var',!nbvars,'t',!nbtime). 

!DO !cnt=1 !TO !nbvars.
COMPUTE !CONCAT(v,'((',!cnt,'-1)*',!nbtime,'+time1)')=!CONCAT(var,!cnt,'t').
!DOEND.

AGGREGATE
  /OUTFILE=*
  /BREAK=id
!DO !cnt=1 !TO !nbvars
  /!CONCAT('var',!cnt,'t1') TO  !CONCAT('var',!cnt,'t',!nbtime)=MAX(!CONCAT('var',!cnt,'t1') TO  !CONCAT('var',!cnt,'t',!nbtime))
!DOEND.
 
EXECUTE.
!ENDDEFINE.
*//////////////////////////////////.

SET MPRINT=yes.
* Call macro giving the number of variables and of time periods.
* Note: these 2 varables could be determined by the syntax if required.
!transf nbvars=3 nbtime=4.
Related pages

...

Navigate from here