Transpose multiple records to multiple variables

This function was used to transpose records to variables. 

For example, in a longitudinal study you follow up 200 children and have measured each child’s height at age 2, 4, 6 and 8.  Your data was organized as each child each visit had one record (one line), each record had a height variable and age variable.  Now you want to reshape the data as each line for each child.  You original data had 200*4=800 lines, and you want a new data that had 200 lines.

Using demo.xls as an example, in demo.xls each family (FMYID is family ID) have 1 to 9 subjects, variable NID represent family member relationship (1=father, 2=mother, 11=first child, 12=second child,…), each subject had SBP and DBP measured.

Now you want to reshape the data as one family had one line, in each line there are SBP1, SBP2, SBP11, SBP12, … SBP17 represent SBP for father, mother, first child, second child, and so on.  A screen shot of this example is as below:

A screen shot of sample input window:

ScreenHunter_05 Jan. 02 20.26.gif

Notes:

1.       The left variables list originally lists all variables.  Select the variable and then click “>” or “<” to move the variables between “variables” list to selected list “Variable for visit”, “ID”, “variables for transpose” or “variables to keep”.

2.       In above example, “NID” likes visit, “FMYID” likes subject identification.

3.       Default new variable name is the original variable name plus the value for “visit” (here is NID). You can change the new variables name by select using visit code as suffix or prefix, and also you can right click the visit and change the suffix (or prefix) for the specific visit.  For the above example, below is a illustration of original data lines and corresponding new data lines:

 

Original data lines

FMYID

NID

SBP

DBP

SEX

AGE

1

1

120

80

1

50

1

2

118

72

2

48

1

11

122

78

2

26

1

12

116

76

2

24

1

13

126

82

1

23

2

1

132

86

1

40

2

2

128

76

2

41

2

11

124

84

1

18

2

12

106

66

1

16

 

 

 

 

 

 

New data lines

FMYID

SBP1

SBP2

SBP11

SBP12

SBP13

SEX

AGE

1

120

118

122

116

126

 

2

24

2

132

128

124

106

NA

 

1

16

 

 

 

 

 

 

 

 

 

SBP1, SBP2, SBP11, SBP12, … represent SBP for NID=1, 2, 11, 12, … respectively

 

4.       “SEX”, “AGE”, etc are variables to keep, and the visit for keep is NID=12.  So for FMYID=1 in the above table, for NID=11, SEX=2 and AGE=24,  for FMYID=2 and NID=11, SEX=1 and AGE=16, these value were  copied to new data file.

 

Click “Run”, a new sub-node (node text is the output file name) will be added to left tree view under node “Data”. 

 

View output files

 

Right click the node, and then select the output file to view.

 

ScreenHunter_06 Jan. 02 20.28.gif