Transpose: Wide to Long

[rebeccachau]

Hi I'm having trouble transposing my wide-format data back to long form. I need to include a variable, TIME, which can be either "1" or "2". The code I am using successfully codes my variables back to the long form, but I can't work out how to get it to add "TIME" as a column as well. Currently each variable in the long form either has a "1" or "2" at the end of the name to denote TIME.

Each ID has a Time "1" and a Time "2", so the long form should look like this:

ID TIME VARIABLE 

1234 1 etc

1234 2

4321 1

4321 2

Here is my code:

DATA LONGGEE;  

SET WORK.IMPORT5;    

TIME=1

ADL=ADL1;

COGTOTSAS=COGTOTSAS1;

FAC=FAC1;

GDS=GDS1;

NHP=NHP1;

PEFITTOTSAS=PEFITTOTSAS1;

SD=SD1;

SOCTOTSAS=SOCTOTSAS1;

OUTPUT;

TIME=2

ADL=ADL2;

COGTOTSAS=COGTOTSAS2;

FAC=FAC2;

GDS=GDS2;

NHP=NHP2;

PEFITTOTSAS=PEFITTOTSAS2;

SD=SD2;

SOCTOTSAS=SOCTOTSAS2;    

OUTPUT;    

KEEP Obs _Imputation_ ID ADL COGTOTSAS FAC GDS NHP PEFITTOTSAS SD SOCTOTSAS TIME;

RUN;

Thank you!

[Reeza]

Are you sure? Your code looks correct, can you post your log and output?

Your KEEP statement should be dropping all your 1/2 variables. 

---

@rebeccachau wrote:

Hi I'm having trouble transposing my wide-format data back to long form. I need to include a variable, TIME, which can be either "1" or "2". The code I am using successfully codes my variables back to the long form, but I can't work out how to get it to add "TIME" as a column as well. Currently each variable in the long form either has a "1" or "2" at the end of the name to denote TIME.

Each ID has a Time "1" and a Time "2", so the long form should look like this:

ID TIME VARIABLE 

1234 1 etc

1234 2

4321 1

4321 2

Here is my code:

DATA LONGGEE;  

SET WORK.IMPORT5;    

TIME=1

ADL=ADL1;

COGTOTSAS=COGTOTSAS1;

FAC=FAC1;

GDS=GDS1;

NHP=NHP1;

PEFITTOTSAS=PEFITTOTSAS1;

SD=SD1;

SOCTOTSAS=SOCTOTSAS1;

OUTPUT;

TIME=2

ADL=ADL2;

COGTOTSAS=COGTOTSAS2;

FAC=FAC2;

GDS=GDS2;

NHP=NHP2;

PEFITTOTSAS=PEFITTOTSAS2;

SD=SD2;

SOCTOTSAS=SOCTOTSAS2;    

OUTPUT;    

KEEP Obs _Imputation_ ID ADL COGTOTSAS FAC GDS NHP PEFITTOTSAS SD SOCTOTSAS;

RUN;

Thank you!

---

[rebeccachau]

Hi,

It's not producing any output because of errors. I have also attached the data in case that's helpful.

Here is the log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73 /* Generated Code (IMPORT) */

74 /* Source File: 02-MVN-MI-HYP1-SUMMED-REP-MEAS.xlsx */

75 /* Source Path: /folders/myfolders */

76 /* Code generated on: 22/01/2019 13:28 */

77

78 %web_drop_table(WORK.IMPORT7);

NOTE: Table WORK.IMPORT7 has been dropped.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

79

80

81 FILENAME REFFILE '/folders/myfolders/02-MVN-MI-HYP1-SUMMED-REP-MEAS.xlsx';

82

83 PROC IMPORT DATAFILE=REFFILE

84 DBMS=XLSX

85 OUT=WORK.IMPORT7;

86 GETNAMES=YES;

87 RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with

options MSGLEVEL=I.

NOTE: The import data set has 2025 observations and 21 variables.

NOTE: WORK.IMPORT7 data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.57 seconds

cpu time 0.54 seconds

88

89 PROC CONTENTS DATA=WORK.IMPORT7; RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.17 seconds

cpu time 0.15 seconds

90

91

92 %web_open_table(WORK.IMPORT7);

93

94 DATA WORK.IMPORT7;

95 SET LONG;

96 TIME=1

97 ADL=ADL1;

___

22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,

IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

98 COGTOTSAS=COGTOTSAS1;

99 FAC=FAC1;

100 GDS=GDS1;

101 NHP=NHP1;

102 PEFITTOTSAS=PEFITTOTSAS1;

103 SD=SD1;

104 SOCTOTSAS=SOCTOTSAS1;

105 OUTPUT;

106

107 TIME=2

108 ADL=ADL2;

___

22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,

IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.

109 COGTOTSAS=COGTOTSAS2;

110 FAC=FAC2;

111 GDS=GDS2;

112 NHP=NHP2;

113 PEFITTOTSAS=PEFITTOTSAS2;

114 SD=SD2;

115 SOCTOTSAS=SOCTOTSAS2;

116 OUTPUT;

117

118 KEEP Obs _Imputation_ ID ADL COGTOTSAS FAC GDS NHP PEFITTOTSAS SD SOCTOTSAS TIME;

119 RUN;

WARNING: The variable Obs in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable _Imputation_ in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.IMPORT7 may be incomplete. When this step was stopped there were 0 observations and 10 variables.

WARNING: Data set WORK.IMPORT7 was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

120

121

122

123

124

125

126

127 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

140

[andreas_lds · Accepted solution]

you need to add a semicolon after

TIME=1;

[rebeccachau]

Cripes! Thanks so much.