topic Re: Reshaping data from long to wide: one item's data reading in blank in SAS Studio

Reshaping data from long to wide: one item's data reading in blank

rebeccachau — Sun, 11 Nov 2018 23:49:21 GMT

I have a long-format raw data set sorted by ID and Time (wave 1, wave 2) and 53 items. The code I have written successfully reshapes the data from long to wide, with each item re-coded by ID and Time so that each item's name is then annotated with "1" or "2" to indicate Time/Wave. For example, "GDS1" (item 1 on the Geriatric Depression Scale) then becomes "GDS11" (Time 1) and "GDS12" (Time 2). Strangely, however, the data for item GDS121 (GDS item 12, Time 1) ONLY reads in completely blank. I do not have this problem with any other item. I have checked the raw data file and the data is clearly there. Any ideas what might be happening here? I have copied my code in below. Thanks!

EXAMPLE DATA

ID	TIME	GDS1	GDS2	GDS3	GDS4	GDS5	GDS6	GDS7	GDS8	GDS9	GDS10	GDS11	GDS12	GDS13	GDS14	GDS15
9464	2
9464	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
5741	1	1	1	1	1	0	1	0	1	1	0	0	1	1	0	0
2689	2
2689	1	0	0	1	1	0	0	1	0	0	0	0	1	1	0	0
9070	1	1	0	1	1	0	1	0	1	0	0	0	1	1	1	0
2115	1	0	0	0	1	0	0	0	1	0	0	0	1	1	0	0
2183	1	0	1	1	1	1	0	1	0	0	0	1	0	1	1	0
3618	1	0	1	0	0	0	0	0	0	0	0	0	0	1	0	0
7050	2
7050	1	0	1	1	1	0	0	0	1	0	0	0	0	1	0	0
4185	1	0	1	0	0	0	1	0	0	1	0	0	1	1	1	0
9465	2
9465	1	0	1	0	0	0	0	0	0	0	0	1	1	1	1	1
7262	1	1	1	0	0	0	0	0	0	0	0	0	0	1	0	0
6375	2
6375	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0
3977	1	0	1	0	0	0	1	0	0	0	0	0	0	1	0	1
4593	1	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0
9582	2
9582	1	1	1	1	0	1	0	0	1	0	1	0	1	1	1	1
2882	2
2882	1	1	0	1	1	1	0	0	0	1	0	0	1	1	0	1
8736	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9774	2
9774	1	0	0	0	0	0	0	0	0	0	1	0	1	0	0	0
1736	2
1736	1	0	1	0	0	0	0	0	1	0	0	0	1	1	1	0

PROC SORT DATA=WORK.IMPORT OUT=WIDESORT ;

BY ID Time ;

RUN ;

DATA WIDE ;

SET WIDESORT ;

BY ID Time ;

KEEP ID COGTOTSAS1 COGTOTSAS2 NHP11 NHP21 NHP31 NHP41 NHP51 NHP12 NHP22 NHP32 NHP42 NHP52 SD11 SD21 SD31 SD41 SD51 SD61 SD71 SD81 SD12 SD22 SD32 SD42 SD52 SD62 SD72 SD82 SOCTOTSAS1 SOCTOTSAS2 ME11 ME21 ME31 ME12 ME22 ME32 PMS11 PMS21 PMS31 PMS41 PMS51 PMS61 PMS71 PMS12 PMS22 PMS32 PMS42 PMS52 PMS62 PMS72 PEFITTOTSAS1 PEFITTOTSAS2 GDS11 GDS21 GDS31 GDS41 GDS51 GDS61 GDS71 GDS81 GDS91 GDS101 GDS111 GDS121 GDS131 GDS141 GDS151 GDS12 GDS22 GDS32 GDS42 GDS52 GDS62 GDS72 GDS82 GDS92 GDS102 GDS112 GDS122 GDS132 GDS142 GDS152 RECDEP1 RECDEP2 ADL11 ADL21 ADL31 ADL41 ADL51 ADL61 ADL71 ADL81 ADL91 ADL101 ADL12 ADL22 ADL32 ADL42 ADL52 ADL62 ADL72 ADL82 ADL92 ADL102 CURMDESAS1 CURMDESAS2;

RETAIN COGTOTSAS1 COGTOTSAS2 NHP11 NHP21 NHP31 NHP41 NHP51 NHP12 NHP22 NHP32 NHP42 NHP52 SD11 SD21 SD31 SD41 SD51 SD61 SD71 SD81 SD12 SD22 SD32 SD42 SD52 SD62 SD72 SD82 SOCTOTSAS1 SOCTOTSAS2 ME11 ME21 ME31 ME12 ME22 ME32 PMS11 PMS21 PMS31 PMS41 PMS51 PMS61 PMS71 PMS12 PMS22 PMS32 PMS42 PMS52 PMS62 PMS72 PEFITTOTSAS1 PEFITTOTSAS2 GDS11 GDS21 GDS31 GDS41 GDS51 GDS61 GDS71 GDS81 GDS91 GDS101 GDS111 GDS121 GDS131 GDS141 GDS151 GDS12 GDS22 GDS32 GDS42 GDS52 GDS62 GDS72 GDS82 GDS92 GDS102 GDS112 GDS122 GDS132 GDS142 GDS152 RECDEP1 RECDEP2 ADL11 ADL21 ADL31 ADL41 ADL51 ADL61 ADL71 ADL81 ADL91 ADL101 ADL12 ADL22 ADL32 ADL42 ADL52 ADL62 ADL72 ADL82 ADL92 ADL102 CURMDESAS1 CURMDESAS2;

ARRAY aCOGTOTSAS(1:2) COGTOTSAS1 COGTOTSAS2;

ARRAY aNHP1 (1:2) NHP11 NHP12;

ARRAY aNHP2 (1:2) NHP21 NHP22;

ARRAY aNHP3 (1:2) NHP31 NHP32;

ARRAY aNHP4 (1:2) NHP41 NHP42;

ARRAY aNHP5 (1:2) NHP51 NHP52;

ARRAY aSD1 (1:2) SD11 SD12;

ARRAY aSD2 (1:2) SD21 SD22;

ARRAY aSD3 (1:2) SD31 SD32;

ARRAY aSD4 (1:2) SD41 SD42;

ARRAY aSD5 (1:2) SD51 SD52;

ARRAY aSD6 (1:2) SD61 SD62;

ARRAY aSD7 (1:2) SD71 SD72;

ARRAY aSD8 (1:2) SD81 SD82;

ARRAY aSOCTOTSAS (1:2) SOCTOTSAS1 SOCTOTSAS2;

ARRAY aME1 (1:2) ME11 ME12;

ARRAY aME2 (1:2) ME21 ME22;

ARRAY aME3 (1:2) ME31 ME32;

ARRAY aPMS1(1:2) PMS11 PMS12;

ARRAY aPMS2(1:2) PMS21 PMS22;

ARRAY aPMS3(1:2) PMS31 PMS32;

ARRAY aPMS4(1:2) PMS41 PMS42;

ARRAY aPMS5(1:2) PMS51 PMS52;

ARRAY aPMS6(1:2) PMS61 PMS62;

ARRAY aPMS7(1:2) PMS71 PMS72;

ARRAY aPEFITTOTSAS(1:2) PEFITTOTSAS1 PEFITTOTSAS2;

ARRAY aGDS1(1:2) GDS11 GDS12;

ARRAY aGDS2(1:2) GDS21 GDS22;

ARRAY aGDS3(1:2) GDS31 GDS32;

ARRAY aGDS4(1:2) GDS41 GDS42;

ARRAY aGDS5(1:2) GDS51 GDS52;

ARRAY aGDS6(1:2) GDS61 GDS62;

ARRAY aGDS7(1:2) GDS71 GDS72;

ARRAY aGDS8(1:2) GDS81 GDS82;

ARRAY aGDS9(1:2) GDS91 GDS92;

ARRAY aGDS10(1:2) GDS101 GDS102;

ARRAY aGDS11(1:2) GDS111 GDS112;

ARRAY aGDS12(1:2) GDS121 GDS122;

ARRAY aGDS13(1:2) GDS131 GDS132;

ARRAY aGDS14(1:2) GDS141 GDS142;

ARRAY aGDS15(1:2) GDS151 GDS152;

ARRAY aRECDEP(1:2) RECDEP1 RECDEP2;

ARRAY aADL1 (1:2) ADL11 ADL12;

ARRAY aADL2 (1:2) ADL21 ADL22;

ARRAY aADL3 (1:2) ADL31 ADL32;

ARRAY aADL4 (1:2) ADL41 ADL42;

ARRAY aADL5 (1:2) ADL51 ADL52;

ARRAY aADL6 (1:2) ADL61 ADL62;

ARRAY aADL7 (1:2) ADL71 ADL72;

ARRAY aADL8 (1:2) ADL81 ADL82;

ARRAY aADL9 (1:2) ADL91 ADL92;

ARRAY aADL10 (1:2) ADL101 ADL102;

ARRAY aCURMDESAS (1:2) CURMDESAS1 CURMDESAS2;

IF first.ID THEN

DO;

DO i = 1 to 2 ;

aCOGTOTSAS( i ) = . ;

aNHP1( i ) = . ;

aNHP2( i ) = . ;

aNHP3( i ) = . ;

aNHP4( i ) = . ;

aNHP5( i ) = . ;

aSD1( i ) = . ;

aSD2( i ) = . ;

aSD3( i ) = . ;

aSD4( i ) = . ;

aSD5( i ) = . ;

aSD6( i ) = . ;

aSD7( i ) = . ;

aSD8( i ) = . ;

aSOCTOTSAS( i ) = . ;

aME1( i ) = . ;

aME2( i ) = . ;

aME3( i ) = . ;

aPMS1( i ) = . ;

aPMS2( i ) = . ;

aPMS3( i ) = . ;

aPMS4( i ) = . ;

aPMS5( i ) = . ;

aPMS6( i ) = . ;

aPMS7( i ) = . ;

aPEFITTOTSAS( i ) = . ;

aGDS1( i ) = . ;

aGDS2( i ) = . ;

aGDS3( i ) = . ;

aGDS4( i ) = . ;

aGDS5( i ) = . ;

aGDS6( i ) = . ;

aGDS7( i ) = . ;

aGDS8( i ) = . ;

aGDS9( i ) = . ;

aGDS10( i ) = . ;

aGDS11( i ) = . ;

aGDS12( i ) = . ;

aGDS13( i ) = . ;

aGDS14( i ) = . ;

aGDS15( i ) = . ;

aRECDEP( i ) = . ;

aCURMDESAS( i ) = . ;

END;

aCOGTOTSAS( Time ) = COGTOTSAS ;

aNHP1( Time ) = NHP1 ;

aNHP2( Time ) = NHP2 ;

aNHP3( Time ) = NHP3 ;

aNHP4( Time ) = NHP4 ;

aNHP5( Time ) = NHP5 ;

aSD1( Time ) = SD1 ;

aSD2( Time ) = SD2 ;

aSD3( Time ) = SD3 ;

aSD4( Time ) = SD4 ;

aSD5( Time ) = SD5 ;

aSD6( Time ) = SD6 ;

aSD7( Time ) = SD7 ;

aSD8( Time ) = SD8 ;

aSOCTOTSAS( Time ) = SOCTOTSAS ;

aME1( Time ) = ME1 ;

aME2( Time ) = ME2 ;

aME3( Time ) = ME3 ;

aPMS1( Time ) = PMS1 ;

aPMS2( Time ) = PMS2 ;

aPMS3( Time ) = PMS3 ;

aPMS4( Time ) = PMS4 ;

aPMS5( Time ) = PMS5 ;

aPMS6( Time ) = PMS6 ;

aPMS7( Time ) = PMS7 ;

aPEFITTOTSAS( Time ) = PEFITTOTSAS ;

aGDS1( Time ) = GDS1 ;

aGDS2( Time ) = GDS2 ;

aGDS3( Time ) = GDS3 ;

aGDS4( Time ) = GDS4 ;

aGDS5( Time ) = GDS5 ;

aGDS6( Time ) = GDS6 ;

aGDS7( Time ) = GDS7 ;

aGDS8( Time ) = GDS8 ;

aGDS9( Time ) = GDS9 ;

aGDS10( Time ) = GDS10 ;

aGDS11( Time ) = GDS11 ;

aGDS12( Time ) = GDS12 ;

aGDS13( Time ) = GDS13 ;

aGDS14( Time ) = GDS14 ;

aGDS15( Time ) = GDS15 ;

aRECDEP( Time ) = RECDEP ;

aADL1( Time ) = ADL1 ;

aADL2( Time ) = ADL2 ;

aADL3( Time ) = ADL3 ;

aADL4( Time ) = ADL4 ;

aADL5( Time ) = ADL5 ;

aADL6( Time ) = ADL6 ;

aADL7( Time ) = ADL7 ;

aADL8( Time ) = ADL8 ;

aADL9( Time ) = ADL9 ;

aADL10( Time ) = ADL10 ;

aCURMDESAS( Time ) = CURMDESAS ;

IF last.ID THEN OUTPUT ;

RUN;

proc print data=WIDESORT;

run;

Re: Reshaping data from long to wide: one item's data reading in blank

Reeza — Mon, 12 Nov 2018 01:45:09 GMT

For setting a list of variables to missing at once you can use

call missing (of gds10);

Though, I wonder if having a 2 dimensional array would't have been better than the multiple array declarations.

Re: Reshaping data from long to wide: one item's data reading in blank

rebeccachau — Mon, 12 Nov 2018 03:04:43 GMT

Thanks, but my question is why does does one item (GDS121) come through blank with the reshaping when it should have data in it. The item GDS122 (for Time 2) comes through fine. It seems like an SAS bug. ??

Re: Reshaping data from long to wide: one item's data reading in blank

Reeza — Mon, 12 Nov 2018 03:29:44 GMT

It’s 99% likely there’s a mistake in your code rather than a SAS bug. Without the data I can’t run your code. Also, I’ve done this about 1000 times and never run into a bug, though it is possible there is one.

Since everything is in pairs, you can try searching for the pair of that variable and see if it’s not being set correctly somewhere. Part of the problem with really long code - make it hard to find the mistake. If you show what your original data was or a reproducible example Someone may be able to see the issue. Including at least the log would be helpful. Is there no note about uninitialized variable or something? Or missing? Or conversions?

Re: Reshaping data from long to wide: one item's data reading in blank

Reeza — Mon, 12 Nov 2018 03:30:54 GMT

Any reason you’re not using PROC TRANSPOSE With two ID variables?

Re: Reshaping data from long to wide: one item's data reading in blank

rebeccachau — Mon, 12 Nov 2018 03:58:12 GMT

Hi, I just couldn't get PROC TRANSPOSE to work for the life of me! I have attached the data.

Thanks.

Here is the log using the code supplied earlier:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

74 /* Generated Code (IMPORT) */

75 /* Source File: WIDEFORMATDATA1.xlsx */

76 /* Source Path: /folders/myfolders */

77 /* Code generated on: 12/11/2018 11:53 */

79 %web_drop_table(WORK.IMPORT);

82 FILENAME REFFILE '/folders/myfolders/WIDEFORMATDATA1.xlsx';

84 PROC IMPORT DATAFILE=REFFILE

85 DBMS=XLSX

86 OUT=WORK.IMPORT;

87 GETNAMES=YES;

88 RUN;

NOTE: The import data set has 294 observations and 55 variables.

NOTE: WORK.IMPORT data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.35 seconds

cpu time 0.34 seconds

90 PROC CONTENTS DATA=WORK.IMPORT; RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.23 seconds

cpu time 0.22 seconds

93 %web_open_table(WORK.IMPORT);

95 PROC SORT DATA=WORK.IMPORT OUT=WIDESORT ;

96 BY ID Time ;

97 RUN ;

NOTE: There were 294 observations read from the data set WORK.IMPORT.

NOTE: The data set WORK.WIDESORT has 294 observations and 55 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

99 DATA WIDE ;

100 SET WIDESORT ;

101 BY ID Time ;

102

103 KEEP ID COGTOTSAS1 COGTOTSAS2 NHP11 NHP21 NHP31 NHP41 NHP51 NHP12 NHP22 NHP32 NHP42 NHP52 SD11 SD21 SD31 SD41 SD51 SD61

103 ! SD71 SD81 SD12 SD22 SD32 SD42 SD52 SD62 SD72 SD82 SOCTOTSAS1 SOCTOTSAS2 ME11 ME21 ME31 ME12 ME22 ME32 PMS11 PMS21 PMS31

103 ! PMS41 PMS51 PMS61 PMS71 PMS12 PMS22 PMS32 PMS42 PMS52 PMS62 PMS72 PEFITTOTSAS1 PEFITTOTSAS2 GDS11 GDS21 GDS31 GDS41 GDS51

103 ! GDS61 GDS71 GDS81 GDS91 GDS101 GDS111 GDS121 GDS131 GDS141 GDS151 GDS12 GDS22 GDS32 GDS42 GDS52 GDS62 GDS72 GDS82 GDS92

103 ! GDS102 GDS112 GDS122 GDS132 GDS142 GDS152 RECDEP1 RECDEP2 ADL11 ADL21 ADL31 ADL41 ADL51 ADL61 ADL71 ADL81 ADL91 ADL101

103 ! ADL12 ADL22 ADL32 ADL42 ADL52 ADL62 ADL72 ADL82 ADL92 ADL102 CURMDESAS1 CURMDESAS2;

104 RETAIN COGTOTSAS1 COGTOTSAS2 NHP11 NHP21 NHP31 NHP41 NHP51 NHP12 NHP22 NHP32 NHP42 NHP52 SD11 SD21 SD31 SD41 SD51 SD61

104 ! SD71 SD81 SD12 SD22 SD32 SD42 SD52 SD62 SD72 SD82 SOCTOTSAS1 SOCTOTSAS2 ME11 ME21 ME31 ME12 ME22 ME32 PMS11 PMS21 PMS31

104 ! PMS41 PMS51 PMS61 PMS71 PMS12 PMS22 PMS32 PMS42 PMS52 PMS62 PMS72 PEFITTOTSAS1 PEFITTOTSAS2 GDS11 GDS21 GDS31 GDS41 GDS51

104 ! GDS61 GDS71 GDS81 GDS91 GDS101 GDS111 GDS121 GDS131 GDS141 GDS151 GDS12 GDS22 GDS32 GDS42 GDS52 GDS62 GDS72 GDS82 GDS92

104 ! GDS102 GDS112 GDS122 GDS132 GDS142 GDS152 RECDEP1 RECDEP2 ADL11 ADL21 ADL31 ADL41 ADL51 ADL61 ADL71 ADL81 ADL91 ADL101

104 ! ADL12 ADL22 ADL32 ADL42 ADL52 ADL62 ADL72 ADL82 ADL92 ADL102 CURMDESAS1 CURMDESAS2;

105

106 ARRAY aCOGTOTSAS(1:2) COGTOTSAS1 COGTOTSAS2;

107 ARRAY aNHP1 (1:2) NHP11 NHP12;

108 ARRAY aNHP2 (1:2) NHP21 NHP22;

109 ARRAY aNHP3 (1:2) NHP31 NHP32;

110 ARRAY aNHP4 (1:2) NHP41 NHP42;

111 ARRAY aNHP5 (1:2) NHP51 NHP52;

112 ARRAY aSD1 (1:2) SD11 SD12;

113 ARRAY aSD2 (1:2) SD21 SD22;

114 ARRAY aSD3 (1:2) SD31 SD32;

115 ARRAY aSD4 (1:2) SD41 SD42;

116 ARRAY aSD5 (1:2) SD51 SD52;

117 ARRAY aSD6 (1:2) SD61 SD62;

118 ARRAY aSD7 (1:2) SD71 SD72;

119 ARRAY aSD8 (1:2) SD81 SD82;

120 ARRAY aSOCTOTSAS (1:2) SOCTOTSAS1 SOCTOTSAS2;

121 ARRAY aME1 (1:2) ME11 ME12;

122 ARRAY aME2 (1:2) ME21 ME22;

123 ARRAY aME3 (1:2) ME31 ME32;

124 ARRAY aPMS1(1:2) PMS11 PMS12;

125 ARRAY aPMS2(1:2) PMS21 PMS22;

126 ARRAY aPMS3(1:2) PMS31 PMS32;

127 ARRAY aPMS4(1:2) PMS41 PMS42;

128 ARRAY aPMS5(1:2) PMS51 PMS52;

129 ARRAY aPMS6(1:2) PMS61 PMS62;

130 ARRAY aPMS7(1:2) PMS71 PMS72;

131 ARRAY aPEFITTOTSAS(1:2) PEFITTOTSAS1 PEFITTOTSAS2;

132 ARRAY aGDS1(1:2) GDS11 GDS12;

133 ARRAY aGDS2(1:2) GDS21 GDS22;

134 ARRAY aGDS3(1:2) GDS31 GDS32;

135 ARRAY aGDS4(1:2) GDS41 GDS42;

136 ARRAY aGDS5(1:2) GDS51 GDS52;

137 ARRAY aGDS6(1:2) GDS61 GDS62;

138 ARRAY aGDS7(1:2) GDS71 GDS72;

139 ARRAY aGDS8(1:2) GDS81 GDS82;

140 ARRAY aGDS9(1:2) GDS91 GDS92;

141 ARRAY aGDS10(1:2) GDS101 GDS102;

142 ARRAY aGDS11(1:2) GDS111 GDS112;

143 ARRAY aGDS12(1:2) GDS121 GDS122;

144 ARRAY aGDS13(1:2) GDS131 GDS132;

145 ARRAY aGDS14(1:2) GDS141 GDS142;

146 ARRAY aGDS15(1:2) GDS151 GDS152;

147 ARRAY aRECDEP(1:2) RECDEP1 RECDEP2;

148 ARRAY aADL1 (1:2) ADL11 ADL12;

149 ARRAY aADL2 (1:2) ADL21 ADL22;

150 ARRAY aADL3 (1:2) ADL31 ADL32;

151 ARRAY aADL4 (1:2) ADL41 ADL42;

152 ARRAY aADL5 (1:2) ADL51 ADL52;

153 ARRAY aADL6 (1:2) ADL61 ADL62;

154 ARRAY aADL7 (1:2) ADL71 ADL72;

155 ARRAY aADL8 (1:2) ADL81 ADL82;

156 ARRAY aADL9 (1:2) ADL91 ADL92;

157 ARRAY aADL10 (1:2) ADL101 ADL102;

158 ARRAY aCURMDESAS (1:2) CURMDESAS1 CURMDESAS2;

159

160

161

162

163

164 IF first.ID THEN

165 DO;

166 DO i = 1 to 2 ;

167 aCOGTOTSAS( i ) = . ;

168 aNHP1( i ) = . ;

169 aNHP2( i ) = . ;

170 aNHP3( i ) = . ;

171 aNHP4( i ) = . ;

172 aNHP5( i ) = . ;

173 aSD1( i ) = . ;

174 aSD2( i ) = . ;

175 aSD3( i ) = . ;

176 aSD4( i ) = . ;

177 aSD5( i ) = . ;

178 aSD6( i ) = . ;

179 aSD7( i ) = . ;

180 aSD8( i ) = . ;

181 aSOCTOTSAS( i ) = . ;

182 aME1( i ) = . ;

183 aME2( i ) = . ;

184 aME3( i ) = . ;

185 aPMS1( i ) = . ;

186 aPMS2( i ) = . ;

187 aPMS3( i ) = . ;

188 aPMS4( i ) = . ;

189 aPMS5( i ) = . ;

190 aPMS6( i ) = . ;

191 aPMS7( i ) = . ;

192 aPEFITTOTSAS( i ) = . ;

193 aGDS1( i ) = . ;

194 aGDS2( i ) = . ;

195 aGDS3( i ) = . ;

196 aGDS4( i ) = . ;

197 aGDS5( i ) = . ;

198 aGDS6( i ) = . ;

199 aGDS7( i ) = . ;

200 aGDS8( i ) = . ;

201 aGDS9( i ) = . ;

202 aGDS10( i ) = . ;

203 aGDS11( i ) = . ;

204 aGDS12( i ) = . ;

205 aGDS13( i ) = . ;

206 aGDS14( i ) = . ;

207 aGDS15( i ) = . ;

208 aRECDEP( i ) = . ;

209 aCURMDESAS( i ) = . ;

210

211

212 END;

213 END;

214

215 aCOGTOTSAS( Time ) = COGTOTSAS ;

216 aNHP1( Time ) = NHP1 ;

217 aNHP2( Time ) = NHP2 ;

218 aNHP3( Time ) = NHP3 ;

219 aNHP4( Time ) = NHP4 ;

220 aNHP5( Time ) = NHP5 ;

221 aSD1( Time ) = SD1 ;

222 aSD2( Time ) = SD2 ;

223 aSD3( Time ) = SD3 ;

224 aSD4( Time ) = SD4 ;

225 aSD5( Time ) = SD5 ;

226 aSD6( Time ) = SD6 ;

227 aSD7( Time ) = SD7 ;

228 aSD8( Time ) = SD8 ;

229 aSOCTOTSAS( Time ) = SOCTOTSAS ;

230 aME1( Time ) = ME1 ;

231 aME2( Time ) = ME2 ;

232 aME3( Time ) = ME3 ;

233 aPMS1( Time ) = PMS1 ;

234 aPMS2( Time ) = PMS2 ;

235 aPMS3( Time ) = PMS3 ;

236 aPMS4( Time ) = PMS4 ;

237 aPMS5( Time ) = PMS5 ;

238 aPMS6( Time ) = PMS6 ;

239 aPMS7( Time ) = PMS7 ;

240 aPEFITTOTSAS( Time ) = PEFITTOTSAS ;

241 aGDS1( Time ) = GDS1 ;

242 aGDS2( Time ) = GDS2 ;

243 aGDS3( Time ) = GDS3 ;

244 aGDS4( Time ) = GDS4 ;

245 aGDS5( Time ) = GDS5 ;

246 aGDS6( Time ) = GDS6 ;

247 aGDS7( Time ) = GDS7 ;

248 aGDS8( Time ) = GDS8 ;

249 aGDS9( Time ) = GDS9 ;

250 aGDS10( Time ) = GDS10 ;

251 aGDS11( Time ) = GDS11 ;

252 aGDS12( Time ) = GDS12 ;

253 aGDS13( Time ) = GDS13 ;

254 aGDS14( Time ) = GDS14 ;

255 aGDS15( Time ) = GDS15 ;

256 aRECDEP( Time ) = RECDEP ;

257 aADL1( Time ) = ADL1 ;

258 aADL2( Time ) = ADL2 ;

259 aADL3( Time ) = ADL3 ;

260 aADL4( Time ) = ADL4 ;

261 aADL5( Time ) = ADL5 ;

262 aADL6( Time ) = ADL6 ;

263 aADL7( Time ) = ADL7 ;

264 aADL8( Time ) = ADL8 ;

265 aADL9( Time ) = ADL9 ;

266 aADL10( Time ) = ADL10 ;

267 aCURMDESAS( Time ) = CURMDESAS ;

268

269 IF last.ID THEN OUTPUT ;

270 RUN;

NOTE: There were 294 observations read from the data set WORK.WIDESORT.

NOTE: The data set WORK.WIDE has 147 observations and 107 variables.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

271

272 proc print data=WIDESORT;

273 run;

NOTE: There were 294 observations read from the data set WORK.WIDESORT.

NOTE: PROCEDURE PRINT used (Total process time):

real time 7.90 seconds

cpu time 7.88 seconds

274

275

276

277 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

290

Re: Reshaping data from long to wide: one item's data reading in blank

Reeza — Mon, 12 Nov 2018 05:12:44 GMT

This is an already wide data set... Are you going from wide to wider and combining the time only? With two times?

Re: Reshaping data from long to wide: one item's data reading in blank

rebeccachau — Mon, 12 Nov 2018 05:22:53 GMT

Hi, yes, just going wide on time, Time 1 and Time 2

From

ID Time GDS1

1234 1 0

1234 2 0

2345 1 1

2345 2 0

ID GDS11 GDS12

1234 0 0

2345 1 0

Re: Reshaping data from long to wide: one item's data reading in blank

Reeza — Mon, 12 Nov 2018 05:36:04 GMT

So, you're reusing variable names that already exist in your data, in particular in RETAIN and that will cause 'unexpected behaviour' at times. So either make sure your KEEP and RETAIN statements are actually correct or switch to TRANSPOSE. I recommend PROC TRANSPOSE, it's infinitely easier.

Re: Reshaping data from long to wide: one item's data reading in blank

Reeza — Mon, 12 Nov 2018 05:43:56 GMT

For the Transpose like this there's two ways, both infinitely easier than your current approach. One is to merge with a rename. (Edit/Add) Except in your case you are duplicating names again so this approach could get messy. It may be worth naming the variables GDS1_T1 to indicate the time specifically or at least an underscore if using this method.

data want;
merge start (rename = (GDS1 = GDS11 GDS2 = GDS21 ... GDS15=GDS151) where=(time=1))
start (rename = (GDS1=GDS12 GDS2 = GDS22 ... GDS15=GDS152) where=(time=2));
by ID;
run;

The second is a double transpose. First transpose it to a long data set that has the variable name and the time in the column and then transpose to wide again, this time using both TIME and the variable name as the ID.

I renamed your data sets, but I'm sure you can sort it out. Also note the use of variable lists so you don't have to type them all out.

proc import out=wideIn datafile='/folders/myfolders/WIDEFORMATDATA1.xlsx' dbms=xlsx replace;run;

proc sort data=wideIn; by ID time;
run;

proc transpose data=wideIn out=long;
by id time;
var ADL1-ADL10 COGTOTSAS CURMDESAS GDS1-GDS15 ME1-ME3 NHP1-NHP5 PEFITTOTSAS PMS1-PMS7 RECDEP SD1-SD8  ;
run;

proc transpose data=long out=wideout;
by id;
id _name_ time;
var col1;
run;

@rebeccachau wrote:

I have a long-format raw data set sorted by ID and Time (wave 1, wave 2) and 53 items. The code I have written successfully reshapes the data from long to wide, with each item re-coded by ID and Time so that each item's name is then annotated with "1" or "2" to indicate Time/Wave. For example, "GDS1" (item 1 on the Geriatric Depression Scale) then becomes "GDS11" (Time 1) and "GDS12" (Time 2). Strangely, however, the data for item GDS121 (GDS item 12, Time 1) ONLY reads in completely blank. I do not have this problem with any other item. I have checked the raw data file and the data is clearly there. Any ideas what might be happening here? I have copied my code in below. Thanks!

EXAMPLE DATA

ID TIME GDS1 GDS2 GDS3 GDS4 GDS5 GDS6 GDS7 GDS8 GDS9 GDS10 GDS11 GDS12 GDS13 GDS14 GDS15

9464 2

9464 1 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0

5741 1 1 1 1 1 0 1 0 1 1 0 0 1 1 0 0

2689 2

2689 1 0 0 1 1 0 0 1 0 0 0 0 1 1 0 0

9070 1 1 0 1 1 0 1 0 1 0 0 0 1 1 1 0

2115 1 0 0 0 1 0 0 0 1 0 0 0 1 1 0 0

2183 1 0 1 1 1 1 0 1 0 0 0 1 0 1 1 0

3618 1 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0

7050 2

7050 1 0 1 1 1 0 0 0 1 0 0 0 0 1 0 0

4185 1 0 1 0 0 0 1 0 0 1 0 0 1 1 1 0

9465 2

9465 1 0 1 0 0 0 0 0 0 0 0 1 1 1 1 1

7262 1 1 1 0 0 0 0 0 0 0 0 0 0 1 0 0

6375 2

6375 1 0 0 0 0 0 0 0 0 1 0 0 0 1 0 0

3977 1 0 1 0 0 0 1 0 0 0 0 0 0 1 0 1

4593 1 0 0 0 1 0 0 0 0 0 0 0 0 1 0 0

9582 2

9582 1 1 1 1 0 1 0 0 1 0 1 0 1 1 1 1

2882 2

2882 1 1 0 1 1 1 0 0 0 1 0 0 1 1 0 1

8736 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

9774 2

9774 1 0 0 0 0 0 0 0 0 0 1 0 1 0 0 0

1736 2

1736 1 0 1 0 0 0 0 0 1 0 0 0 1 1 1 0

PROC SORT DATA=WORK.IMPORT OUT=WIDESORT ;

BY ID Time ;

RUN ;

DATA WIDE ;

SET WIDESORT ;

BY ID Time ;

KEEP ID COGTOTSAS1 COGTOTSAS2 NHP11 NHP21 NHP31 NHP41 NHP51 NHP12 NHP22 NHP32 NHP42 NHP52 SD11 SD21 SD31 SD41 SD51 SD61 SD71 SD81 SD12 SD22 SD32 SD42 SD52 SD62 SD72 SD82 SOCTOTSAS1 SOCTOTSAS2 ME11 ME21 ME31 ME12 ME22 ME32 PMS11 PMS21 PMS31 PMS41 PMS51 PMS61 PMS71 PMS12 PMS22 PMS32 PMS42 PMS52 PMS62 PMS72 PEFITTOTSAS1 PEFITTOTSAS2 GDS11 GDS21 GDS31 GDS41 GDS51 GDS61 GDS71 GDS81 GDS91 GDS101 GDS111 GDS121 GDS131 GDS141 GDS151 GDS12 GDS22 GDS32 GDS42 GDS52 GDS62 GDS72 GDS82 GDS92 GDS102 GDS112 GDS122 GDS132 GDS142 GDS152 RECDEP1 RECDEP2 ADL11 ADL21 ADL31 ADL41 ADL51 ADL61 ADL71 ADL81 ADL91 ADL101 ADL12 ADL22 ADL32 ADL42 ADL52 ADL62 ADL72 ADL82 ADL92 ADL102 CURMDESAS1 CURMDESAS2;

RETAIN COGTOTSAS1 COGTOTSAS2 NHP11 NHP21 NHP31 NHP41 NHP51 NHP12 NHP22 NHP32 NHP42 NHP52 SD11 SD21 SD31 SD41 SD51 SD61 SD71 SD81 SD12 SD22 SD32 SD42 SD52 SD62 SD72 SD82 SOCTOTSAS1 SOCTOTSAS2 ME11 ME21 ME31 ME12 ME22 ME32 PMS11 PMS21 PMS31 PMS41 PMS51 PMS61 PMS71 PMS12 PMS22 PMS32 PMS42 PMS52 PMS62 PMS72 PEFITTOTSAS1 PEFITTOTSAS2 GDS11 GDS21 GDS31 GDS41 GDS51 GDS61 GDS71 GDS81 GDS91 GDS101 GDS111 GDS121 GDS131 GDS141 GDS151 GDS12 GDS22 GDS32 GDS42 GDS52 GDS62 GDS72 GDS82 GDS92 GDS102 GDS112 GDS122 GDS132 GDS142 GDS152 RECDEP1 RECDEP2 ADL11 ADL21 ADL31 ADL41 ADL51 ADL61 ADL71 ADL81 ADL91 ADL101 ADL12 ADL22 ADL32 ADL42 ADL52 ADL62 ADL72 ADL82 ADL92 ADL102 CURMDESAS1 CURMDESAS2;

ARRAY aCOGTOTSAS(1:2) COGTOTSAS1 COGTOTSAS2;

ARRAY aNHP1 (1:2) NHP11 NHP12;

ARRAY aNHP2 (1:2) NHP21 NHP22;

ARRAY aNHP3 (1:2) NHP31 NHP32;

ARRAY aNHP4 (1:2) NHP41 NHP42;

ARRAY aNHP5 (1:2) NHP51 NHP52;

ARRAY aSD1 (1:2) SD11 SD12;

ARRAY aSD2 (1:2) SD21 SD22;

ARRAY aSD3 (1:2) SD31 SD32;

ARRAY aSD4 (1:2) SD41 SD42;

ARRAY aSD5 (1:2) SD51 SD52;

ARRAY aSD6 (1:2) SD61 SD62;

ARRAY aSD7 (1:2) SD71 SD72;

ARRAY aSD8 (1:2) SD81 SD82;

ARRAY aSOCTOTSAS (1:2) SOCTOTSAS1 SOCTOTSAS2;

ARRAY aME1 (1:2) ME11 ME12;

ARRAY aME2 (1:2) ME21 ME22;

ARRAY aME3 (1:2) ME31 ME32;

ARRAY aPMS1(1:2) PMS11 PMS12;

ARRAY aPMS2(1:2) PMS21 PMS22;

ARRAY aPMS3(1:2) PMS31 PMS32;

ARRAY aPMS4(1:2) PMS41 PMS42;

ARRAY aPMS5(1:2) PMS51 PMS52;

ARRAY aPMS6(1:2) PMS61 PMS62;

ARRAY aPMS7(1:2) PMS71 PMS72;

ARRAY aPEFITTOTSAS(1:2) PEFITTOTSAS1 PEFITTOTSAS2;

ARRAY aGDS1(1:2) GDS11 GDS12;

ARRAY aGDS2(1:2) GDS21 GDS22;

ARRAY aGDS3(1:2) GDS31 GDS32;

ARRAY aGDS4(1:2) GDS41 GDS42;

ARRAY aGDS5(1:2) GDS51 GDS52;

ARRAY aGDS6(1:2) GDS61 GDS62;

ARRAY aGDS7(1:2) GDS71 GDS72;

ARRAY aGDS8(1:2) GDS81 GDS82;

ARRAY aGDS9(1:2) GDS91 GDS92;

ARRAY aGDS10(1:2) GDS101 GDS102;

ARRAY aGDS11(1:2) GDS111 GDS112;

ARRAY aGDS12(1:2) GDS121 GDS122;

ARRAY aGDS13(1:2) GDS131 GDS132;

ARRAY aGDS14(1:2) GDS141 GDS142;

ARRAY aGDS15(1:2) GDS151 GDS152;

ARRAY aRECDEP(1:2) RECDEP1 RECDEP2;

ARRAY aADL1 (1:2) ADL11 ADL12;

ARRAY aADL2 (1:2) ADL21 ADL22;

ARRAY aADL3 (1:2) ADL31 ADL32;

ARRAY aADL4 (1:2) ADL41 ADL42;

ARRAY aADL5 (1:2) ADL51 ADL52;

ARRAY aADL6 (1:2) ADL61 ADL62;

ARRAY aADL7 (1:2) ADL71 ADL72;

ARRAY aADL8 (1:2) ADL81 ADL82;

ARRAY aADL9 (1:2) ADL91 ADL92;

ARRAY aADL10 (1:2) ADL101 ADL102;

ARRAY aCURMDESAS (1:2) CURMDESAS1 CURMDESAS2;

IF first.ID THEN

DO;

   DO i = 1 to 2 ;

     aCOGTOTSAS( i ) = . ;

     aNHP1( i ) = . ;

     aNHP2( i ) = . ;

     aNHP3( i ) = . ;

     aNHP4( i ) = . ;

     aNHP5( i ) = . ;

     aSD1( i ) = . ;

     aSD2( i ) = . ;

     aSD3( i ) = . ;

     aSD4( i ) = . ;

     aSD5( i ) = . ;

     aSD6( i ) = . ;

     aSD7( i ) = . ;

     aSD8( i ) = . ;

     aSOCTOTSAS( i ) = . ;

     aME1( i ) = . ;

     aME2( i ) = . ;

     aME3( i ) = . ;

     aPMS1( i ) = . ;

     aPMS2( i ) = . ;

     aPMS3( i ) = . ;

     aPMS4( i ) = . ;

     aPMS5( i ) = . ;

     aPMS6( i ) = . ;

     aPMS7( i ) = . ;

     aPEFITTOTSAS( i ) = . ;

     aGDS1( i ) = . ;

     aGDS2( i ) = . ;

     aGDS3( i ) = . ;

     aGDS4( i ) = . ;

     aGDS5( i ) = . ;

     aGDS6( i ) = . ;

     aGDS7( i ) = . ;

     aGDS8( i ) = . ;

     aGDS9( i ) = . ;

     aGDS10( i ) = . ;

     aGDS11( i ) = . ;

     aGDS12( i ) = . ;

     aGDS13( i ) = . ;

     aGDS14( i ) = . ;

     aGDS15( i ) = . ;

     aRECDEP( i ) = . ;

     aCURMDESAS( i ) = . ;



   END;

END;

aCOGTOTSAS( Time ) = COGTOTSAS ;

aNHP1( Time ) = NHP1 ;

aNHP2( Time ) = NHP2 ;

aNHP3( Time ) = NHP3 ;

aNHP4( Time ) = NHP4 ;

aNHP5( Time ) = NHP5 ;

aSD1( Time ) = SD1 ;

aSD2( Time ) = SD2 ;

aSD3( Time ) = SD3 ;

aSD4( Time ) = SD4 ;

aSD5( Time ) = SD5 ;

aSD6( Time ) = SD6 ;

aSD7( Time ) = SD7 ;

aSD8( Time ) = SD8 ;

aSOCTOTSAS( Time ) = SOCTOTSAS ;

aME1( Time ) = ME1 ;

aME2( Time ) = ME2 ;

aME3( Time ) = ME3 ;

aPMS1( Time ) = PMS1 ;

aPMS2( Time ) = PMS2 ;

aPMS3( Time ) = PMS3 ;

aPMS4( Time ) = PMS4 ;

aPMS5( Time ) = PMS5 ;

aPMS6( Time ) = PMS6 ;

aPMS7( Time ) = PMS7 ;

aPEFITTOTSAS( Time ) = PEFITTOTSAS ;

aGDS1( Time ) = GDS1 ;

aGDS2( Time ) = GDS2 ;

aGDS3( Time ) = GDS3 ;

aGDS4( Time ) = GDS4 ;

aGDS5( Time ) = GDS5 ;

aGDS6( Time ) = GDS6 ;

aGDS7( Time ) = GDS7 ;

aGDS8( Time ) = GDS8 ;

aGDS9( Time ) = GDS9 ;

aGDS10( Time ) = GDS10 ;

aGDS11( Time ) = GDS11 ;

aGDS12( Time ) = GDS12 ;

aGDS13( Time ) = GDS13 ;

aGDS14( Time ) = GDS14 ;

aGDS15( Time ) = GDS15 ;

aRECDEP( Time ) = RECDEP ;

aADL1( Time ) = ADL1 ;

aADL2( Time ) = ADL2 ;

aADL3( Time ) = ADL3 ;

aADL4( Time ) = ADL4 ;

aADL5( Time ) = ADL5 ;

aADL6( Time ) = ADL6 ;

aADL7( Time ) = ADL7 ;

aADL8( Time ) = ADL8 ;

aADL9( Time ) = ADL9 ;

aADL10( Time ) = ADL10 ;

aCURMDESAS( Time ) = CURMDESAS ;

IF last.ID THEN OUTPUT ;

RUN;

proc print data=WIDESORT;

run;

Re: Reshaping data from long to wide: one item's data reading in blank

rebeccachau — Tue, 13 Nov 2018 02:38:50 GMT

Thank you for the elegant solution! Works perfectly.

Re: Reshaping data from long to wide: one item's data reading in blank

Reeza — Thu, 15 Nov 2018 21:43:40 GMT

Can you please mark this question as solved? Thanks!