Excel date format changes when importing to stata

[Isabel Hostettler]

Hi there,

I've previously not had this problem. I want to import an excel file which contains several variables that are dates into STATA. In excel that dates are formatted as dates (30/05/2012) for example. When I import them into STATA it turns it around and now is 5/30/2012.

How can I import this files without the order of the variable changing? Is there a command that does it? I've read the posts about it but nothing has worked so far.

Thanks very much for the help.

Isabel

[Nick Cox]

The display format can be changed easily in Stata. Experiment shows that this format should be what you want.

. di %tdd/N/CY mdy(5,30,2012)
30/05/2012

Hence use the format command to change the display format to taste.

help format 

help dates

[Mike Lacy]

(My answer and Nick's crossed in the ether.)

Stata apparently defaults to a different display format for the number it imported from Excel that represents the date. To change to the format you want, you can do:

format YourDateVar %tdDD/NN/CCYY

Formats for date aree quite various and complicated, so the easy way to figure out the right format syntax (i.e., what I did) is to call up the format menu with -db format- and select from the choices there.

Regards, Mike

[Isabel Hostettler]

Thank you very much for your answers. Nick's version makes sense but I didn't know how to translate it into my variable. Mike command worked but when looking at the date afterwards it was still the same. I chose from the samples the one that is: 30/04/2009 but although not returning me an error the date still ist 4/30/2009. I could change it to others such as 30 apr 2009 but even then changing it to 30/04/2009 is just not working. I know it's just a preference thing I just can't understand why it never happened before and I can't change it now.

Thanks for your help!

[Nick Cox]

You should show us examples of your data and the exact code you are trying. See FAQ Advice #12.

[Isabel Hostettler]

Okay, I'll try my best.

I used the command:
db format

chose my variable (date1ycontrol)
chose the format
chose the type of date (30/04/2009)
and pressed enter.

the command is:
format %tdDD/NN/CCYY date1ycontrol

But when I look at the date the order is still wrong.




What else can I do?

[Nick Cox]

It looks messed up. For example, 9999 should appear as 18/05/1987. It's possibly imported as a numeric variable with value labels.

As FAQ Advice #12 does advise, you should use dataex (SSC) to show data. Screenshots alone do not tell us storage type, real underlying values and whether value labels are affecting what we see.

[Isabel Hostettler]

Ah, okay. Sorry. That's what comes out with the dataex command. The 9999 is not a date but states the one that did not have a follow up at one year. We had to clearly differentiate them from the ones that had died by this time point. Do you think you see where the problem is?


* Example generated by -dataex-. To install: ssc install dataex
clear
input long date1ycontrol
109
319
101
65
264
264
154
177
325
276
166
325
293
152
205
232
264
264
235
85
240
194
263
70
222
325
325
308
137
209
264
44
290
277
264
325
325
56
5
325
67
264
273
264
325
264
325
325
127
217
130
64
135
201
27
264
325
147
236
264
325
136
231
179
92
46
150
325
270
187
264
282
176
253
325
221
288
301
280
264
115
325
252
312
119
71
325
264
264
214
37
68
189
107
116
325
73
111
103
264
264
293
264
91
16
325
325
325
217
83
16
105
242
2
110
36
264
126
184
264
165
63
264
264
197
143
264
212
48
81
106
234
48
250
264
249
264
325
261
48
229
78
275
264
272
264
250
264
317
264
157
197
18
325
140
264
120
89
311
264
129
264
307
52
76
58
54
239
35
161
173
15
141
118
264
316
69
264
264
192
198
325
264
14
264
24
100
295
246
34
251
325
63
264
75
264
74
80
41
225
325
211
264
108
264
313
325
172
257
117
264
197
6
191
300
227
325
325
112
325
325
40
264
94
264
136
264
99
25
264
60
325
264
170
148
264
264
325
128
61
223
325
264
325
283
163
318
151
164
256
325
325
183
193
113
216
230
264
199
219
248
204
244
199
264
233
239
98
98
43
79
41
11
325
325
325
132
257
144
325
292
190
325
145
17
304
264
323
134
325
322
264
155
264
97
310
264
325
325
264
33
264
4
325
51
264
264
122
264
264
264
123
124
325
19
159
30
325
325
57
264
182
20
264
21
146
167
29
202
315
325
238
196
325
210
218
226
228
237
26
53
39
72
324
45
269
325
268
265
7
325
289
297
84
299
88
62
264
96
325
264
264
325
264
199
163
320
325
168
13
306
1
325
59
66
102
264
264
287
264
158
31
325
138
23
156
325
325
133
254
169
178
262
195
264
264
207
325
264
325
325
245
86
325
213
255
278
247
243
279
274
325
281
325
259
325
298
325
325
303
264
208
310
139
325
264
264
95
57
321
142
264
12
28
125
149
260
325
264
203
325
325
121
325
264
185
175
188
181
220
215
264
22
264
264
264
264
264
260
264
325
286
87
325
267
264
264
8
186
302
294
264
309
186
9
47
50
305
325
264
241
264
264
264
38
264
264
264
264
314
325
264
10
131
325
171
162
77
264
264
153
.
32
.
174
264
266
258
.
104
224
264
291
180
3
.
82
264
49
.
42
.
77
264
.
.
264
206
296
114
.
284
285
264
55
.
271
264
264
200
.
93
264
264
160
.
90
.
end
format %tdDD/NN/CCYY date1ycontrol
label values date1ycontrol date1ycontrol
label def date1ycontrol 1 " 1/4/2007", modify
label def date1ycontrol 2 " 1/4/2013", modify
label def date1ycontrol 3 " 1/5/2015", modify
label def date1ycontrol 4 " 1/7/2008", modify
label def date1ycontrol 5 " 1/7/2014", modify
label def date1ycontrol 6 " 1/8/2013", modify
label def date1ycontrol 7 " 2/3/2009", modify
label def date1ycontrol 8 " 2/5/2007", modify
label def date1ycontrol 9 " 2/6/2007", modify
label def date1ycontrol 10 " 2/7/2014", modify
label def date1ycontrol 11 " 2/8/2011", modify
label def date1ycontrol 12 " 3/1/2006", modify
label def date1ycontrol 13 " 3/2/2009", modify
label def date1ycontrol 14 " 3/2/2011", modify
label def date1ycontrol 15 " 3/5/2010", modify
label def date1ycontrol 16 " 3/6/2013", modify
label def date1ycontrol 17 " 3/8/2011", modify
label def date1ycontrol 18 " 3/9/2012", modify
label def date1ycontrol 19 " 4/1/2008", modify
label def date1ycontrol 20 " 4/2/2008", modify
label def date1ycontrol 21 " 4/3/2008", modify
label def date1ycontrol 22 " 4/4/2007", modify
label def date1ycontrol 23 " 4/5/2007", modify
label def date1ycontrol 24 " 4/6/2010", modify
label def date1ycontrol 25 " 4/6/2011", modify
label def date1ycontrol 26 " 4/8/2011", modify
label def date1ycontrol 27 " 4/9/2014", modify
label def date1ycontrol 28 " 5/1/2007", modify
label def date1ycontrol 29 " 5/2/2008", modify
label def date1ycontrol 30 " 5/7/2008", modify
label def date1ycontrol 31 " 5/8/2007", modify
label def date1ycontrol 32 " 6/2/2014", modify
label def date1ycontrol 33 " 6/3/2008", modify
label def date1ycontrol 34 " 6/6/2011", modify
label def date1ycontrol 35 " 6/8/2010", modify
label def date1ycontrol 36 " 6/9/2011", modify
label def date1ycontrol 37 " 7/1/2013", modify
label def date1ycontrol 38 " 7/3/2014", modify
label def date1ycontrol 39 " 7/8/2008", modify
label def date1ycontrol 40 " 8/2/2013", modify
label def date1ycontrol 41 " 8/4/2010", modify
label def date1ycontrol 42 " 8/4/2014", modify
label def date1ycontrol 43 " 8/6/2009", modify
label def date1ycontrol 44 " 8/6/2013", modify
label def date1ycontrol 45 " 9/4/2008", modify
label def date1ycontrol 46 " 9/4/2012", modify
label def date1ycontrol 47 " 9/6/2007", modify
label def date1ycontrol 48 " 9/6/2011", modify
label def date1ycontrol 49 " 9/8/2014", modify
label def date1ycontrol 50 " 1/10/2007", modify
label def date1ycontrol 51 " 1/10/2008", modify
label def date1ycontrol 52 " 1/12/2012", modify
label def date1ycontrol 53 " 1/15/2009", modify
label def date1ycontrol 54 " 1/18/2010", modify
label def date1ycontrol 55 " 1/19/2015", modify
label def date1ycontrol 56 " 1/20/2014", modify
label def date1ycontrol 57 " 1/21/2008", modify
label def date1ycontrol 58 " 1/22/2010", modify
label def date1ycontrol 59 " 1/25/2007", modify
label def date1ycontrol 60 " 1/27/2009", modify
label def date1ycontrol 61 " 1/27/2010", modify
label def date1ycontrol 62 " 1/28/2009", modify
label def date1ycontrol 63 " 1/28/2011", modify
label def date1ycontrol 64 " 1/28/2014", modify
label def date1ycontrol 65 " 1/29/2014", modify
label def date1ycontrol 66 " 1/30/2007", modify
label def date1ycontrol 67 " 1/30/2014", modify
label def date1ycontrol 68 " 1/31/2014", modify
label def date1ycontrol 69 " 10/1/2012", modify
label def date1ycontrol 70 " 10/1/2013", modify
label def date1ycontrol 71 " 10/2/2012", modify
label def date1ycontrol 72 " 10/3/2008", modify
label def date1ycontrol 73 " 10/3/2012", modify
label def date1ycontrol 74 " 10/5/2010", modify
label def date1ycontrol 75 " 10/7/2010", modify
label def date1ycontrol 76 " 10/9/2012", modify
label def date1ycontrol 77 " 10/9/2014", modify
label def date1ycontrol 78 " 11/1/2011", modify
label def date1ycontrol 79 " 11/2/2009", modify
label def date1ycontrol 80 " 11/2/2010", modify
label def date1ycontrol 81 " 11/2/2011", modify
label def date1ycontrol 82 " 11/3/2014", modify
label def date1ycontrol 83 " 11/5/2013", modify
label def date1ycontrol 84 " 11/6/2008", modify
label def date1ycontrol 85 " 11/7/2013", modify
label def date1ycontrol 86 " 11/8/2007", modify
label def date1ycontrol 87 " 11/9/2006", modify
label def date1ycontrol 88 " 12/2/2008", modify
label def date1ycontrol 89 " 12/2/2011", modify
label def date1ycontrol 90 " 12/2/2014", modify
label def date1ycontrol 91 " 12/3/2012", modify
label def date1ycontrol 92 " 12/4/2011", modify
label def date1ycontrol 93 " 12/5/2014", modify
label def date1ycontrol 94 " 12/6/2011", modify
label def date1ycontrol 95 " 12/7/2007", modify
label def date1ycontrol 96 " 12/7/2008", modify
label def date1ycontrol 97 " 12/8/2008", modify
label def date1ycontrol 98 " 12/8/2009", modify
label def date1ycontrol 99 " 12/8/2010", modify
label def date1ycontrol 100 " 12/9/2010", modify
label def date1ycontrol 101 " 12/9/2013", modify
label def date1ycontrol 102 " 2/10/2007", modify
label def date1ycontrol 103 " 2/10/2013", modify
label def date1ycontrol 104 " 2/10/2015", modify
label def date1ycontrol 105 " 2/11/2013", modify
label def date1ycontrol 106 " 2/13/2012", modify
label def date1ycontrol 107 " 2/13/2013", modify
label def date1ycontrol 108 " 2/14/2012", modify
label def date1ycontrol 109 " 2/14/2013", modify
label def date1ycontrol 110 " 2/15/2011", modify
label def date1ycontrol 111 " 2/15/2013", modify
label def date1ycontrol 112 " 2/16/2011", modify
label def date1ycontrol 113 " 2/18/2010", modify
label def date1ycontrol 114 " 2/18/2015", modify
label def date1ycontrol 115 " 2/19/2013", modify
label def date1ycontrol 116 " 2/20/2013", modify
label def date1ycontrol 117 " 2/21/2011", modify
label def date1ycontrol 118 " 2/22/2011", modify
label def date1ycontrol 119 " 2/22/2013", modify
label def date1ycontrol 120 " 2/23/2012", modify
label def date1ycontrol 121 " 2/24/2006", modify
label def date1ycontrol 122 " 2/27/2008", modify
label def date1ycontrol 123 " 2/28/2008", modify
label def date1ycontrol 124 " 2/29/2008", modify
label def date1ycontrol 125 " 3/10/2006", modify
label def date1ycontrol 126 " 3/10/2011", modify
label def date1ycontrol 127 " 3/10/2014", modify
label def date1ycontrol 128 " 3/11/2009", modify
label def date1ycontrol 129 " 3/12/2012", modify
label def date1ycontrol 130 " 3/12/2014", modify
label def date1ycontrol 131 " 3/14/2014", modify
label def date1ycontrol 132 " 3/16/2010", modify
label def date1ycontrol 133 " 3/21/2007", modify
label def date1ycontrol 134 " 3/22/2010", modify
label def date1ycontrol 135 " 3/25/2014", modify
label def date1ycontrol 136 " 3/26/2012", modify
label def date1ycontrol 137 " 3/26/2014", modify
label def date1ycontrol 138 " 3/27/2007", modify
label def date1ycontrol 139 " 3/27/2008", modify
label def date1ycontrol 140 " 3/27/2012", modify
label def date1ycontrol 141 " 3/29/2010", modify
label def date1ycontrol 142 " 4/10/2007", modify
label def date1ycontrol 143 " 4/12/2011", modify
label def date1ycontrol 144 " 4/13/2010", modify
label def date1ycontrol 145 " 4/14/2010", modify
label def date1ycontrol 146 " 4/15/2010", modify
label def date1ycontrol 147 " 4/15/2014", modify
label def date1ycontrol 148 " 4/16/2009", modify
label def date1ycontrol 149 " 4/19/2006", modify
label def date1ycontrol 150 " 4/19/2012", modify
label def date1ycontrol 151 " 4/22/2009", modify
label def date1ycontrol 152 " 4/23/2013", modify
label def date1ycontrol 153 " 4/23/2014", modify
label def date1ycontrol 154 " 4/24/2013", modify
label def date1ycontrol 155 " 4/25/2011", modify
label def date1ycontrol 156 " 4/26/2007", modify
label def date1ycontrol 157 " 4/26/2012", modify
label def date1ycontrol 158 " 4/27/2007", modify
label def date1ycontrol 159 " 4/28/2008", modify
label def date1ycontrol 160 " 4/28/2015", modify
label def date1ycontrol 161 " 4/29/2010", modify
label def date1ycontrol 162 " 4/29/2014", modify
label def date1ycontrol 163 " 4/30/2009", modify
label def date1ycontrol 164 " 5/12/2009", modify
label def date1ycontrol 165 " 5/12/2011", modify
label def date1ycontrol 166 " 5/13/2013", modify
label def date1ycontrol 167 " 5/15/2008", modify
label def date1ycontrol 168 " 5/15/2009", modify
label def date1ycontrol 169 " 5/18/2007", modify
label def date1ycontrol 170 " 5/18/2009", modify
label def date1ycontrol 171 " 5/18/2015", modify
label def date1ycontrol 172 " 5/21/2012", modify
label def date1ycontrol 173 " 5/22/2010", modify
label def date1ycontrol 174 " 5/23/2014", modify
label def date1ycontrol 175 " 5/24/2006", modify
label def date1ycontrol 176 " 5/24/2012", modify
label def date1ycontrol 177 " 5/24/2013", modify
label def date1ycontrol 178 " 5/25/2007", modify
label def date1ycontrol 179 " 5/25/2012", modify
label def date1ycontrol 180 " 5/25/2015", modify
label def date1ycontrol 181 " 5/26/2006", modify
label def date1ycontrol 182 " 5/26/2008", modify
label def date1ycontrol 183 " 5/26/2009", modify
label def date1ycontrol 184 " 5/27/2011", modify
label def date1ycontrol 185 " 5/29/2006", modify
label def date1ycontrol 186 " 5/31/2007", modify
label def date1ycontrol 187 " 5/31/2012", modify
label def date1ycontrol 188 " 6/12/2006", modify
label def date1ycontrol 189 " 6/13/2013", modify
label def date1ycontrol 190 " 6/15/2010", modify
label def date1ycontrol 191 " 6/15/2011", modify
label def date1ycontrol 192 " 6/16/2010", modify
label def date1ycontrol 193 " 6/17/2009", modify
label def date1ycontrol 194 " 6/17/2013", modify
label def date1ycontrol 195 " 6/19/2007", modify
label def date1ycontrol 196 " 6/19/2008", modify
label def date1ycontrol 197 " 6/21/2011", modify
label def date1ycontrol 198 " 6/22/2010", modify
label def date1ycontrol 199 " 6/23/2009", modify
label def date1ycontrol 200 " 6/23/2015", modify
label def date1ycontrol 201 " 6/25/2014", modify
label def date1ycontrol 202 " 6/28/2008", modify
label def date1ycontrol 203 " 6/29/2006", modify
label def date1ycontrol 204 " 6/30/2009", modify
label def date1ycontrol 205 " 6/30/2013", modify
label def date1ycontrol 206 " 6/30/2015", modify
label def date1ycontrol 207 " 7/10/2007", modify
label def date1ycontrol 208 " 7/10/2008", modify
label def date1ycontrol 209 " 7/10/2013", modify
label def date1ycontrol 210 " 7/14/2008", modify
label def date1ycontrol 211 " 7/15/2011", modify
label def date1ycontrol 212 " 7/16/2011", modify
label def date1ycontrol 213 " 7/17/2007", modify
label def date1ycontrol 214 " 7/18/2013", modify
label def date1ycontrol 215 " 7/21/2006", modify
label def date1ycontrol 216 " 7/22/2009", modify
label def date1ycontrol 217 " 7/22/2013", modify
label def date1ycontrol 218 " 7/24/2008", modify
label def date1ycontrol 219 " 7/24/2009", modify
label def date1ycontrol 220 " 7/26/2006", modify
label def date1ycontrol 221 " 7/26/2012", modify
label def date1ycontrol 222 " 7/26/2013", modify
label def date1ycontrol 223 " 7/27/2009", modify
label def date1ycontrol 224 " 7/28/2014", modify
label def date1ycontrol 225 " 7/30/2010", modify
label def date1ycontrol 226 " 7/31/2008", modify
label def date1ycontrol 227 " 8/10/2010", modify
label def date1ycontrol 228 " 8/15/2008", modify
label def date1ycontrol 229 " 8/16/2011", modify
label def date1ycontrol 230 " 8/17/2009", modify
label def date1ycontrol 231 " 8/17/2012", modify
label def date1ycontrol 232 " 8/20/2013", modify
label def date1ycontrol 233 " 8/22/2011", modify
label def date1ycontrol 234 " 8/23/2011", modify
label def date1ycontrol 235 " 8/23/2013", modify
label def date1ycontrol 236 " 8/24/2012", modify
label def date1ycontrol 237 " 8/25/2008", modify
label def date1ycontrol 238 " 8/26/2008", modify
label def date1ycontrol 239 " 8/27/2010", modify
label def date1ycontrol 240 " 8/28/2013", modify
label def date1ycontrol 241 " 8/28/2014", modify
label def date1ycontrol 242 " 8/29/2012", modify
label def date1ycontrol 243 " 9/10/2007", modify
label def date1ycontrol 244 " 9/10/2009", modify
label def date1ycontrol 245 " 9/13/2007", modify
label def date1ycontrol 246 " 9/13/2010", modify
label def date1ycontrol 247 " 9/14/2007", modify
label def date1ycontrol 248 " 9/14/2009", modify
label def date1ycontrol 249 " 9/14/2011", modify
label def date1ycontrol 250 " 9/15/2011", modify
label def date1ycontrol 251 " 9/16/2010", modify
label def date1ycontrol 252 " 9/18/2012", modify
label def date1ycontrol 253 " 9/19/2012", modify
label def date1ycontrol 254 " 9/20/2007", modify
label def date1ycontrol 255 " 9/21/2007", modify
label def date1ycontrol 256 " 9/21/2009", modify
label def date1ycontrol 257 " 9/22/2010", modify
label def date1ycontrol 258 " 9/22/2014", modify
label def date1ycontrol 259 " 9/25/2008", modify
label def date1ycontrol 260 " 9/26/2006", modify
label def date1ycontrol 261 " 9/26/2011", modify
label def date1ycontrol 262 " 9/28/2007", modify
label def date1ycontrol 263 " 9/30/2013", modify
label def date1ycontrol 264 ".", modify
label def date1ycontrol 265 "10/10/2008", modify
label def date1ycontrol 266 "10/10/2014", modify
label def date1ycontrol 267 "10/13/2006", modify
label def date1ycontrol 268 "10/13/2008", modify
label def date1ycontrol 269 "10/14/2008", modify
label def date1ycontrol 270 "10/15/2012", modify
label def date1ycontrol 271 "10/16/2014", modify
label def date1ycontrol 272 "10/18/2011", modify
label def date1ycontrol 273 "10/18/2013", modify
label def date1ycontrol 274 "10/19/2007", modify
label def date1ycontrol 275 "10/20/2011", modify
label def date1ycontrol 276 "10/21/2013", modify
label def date1ycontrol 277 "10/22/2013", modify
label def date1ycontrol 278 "10/23/2007", modify
label def date1ycontrol 279 "10/24/2007", modify
label def date1ycontrol 280 "10/25/2012", modify
label def date1ycontrol 281 "10/26/2007", modify
label def date1ycontrol 282 "10/26/2012", modify
label def date1ycontrol 283 "10/27/2009", modify
label def date1ycontrol 284 "10/27/2014", modify
label def date1ycontrol 285 "10/28/2014", modify
label def date1ycontrol 286 "10/30/2006", modify
label def date1ycontrol 287 "10/31/2007", modify
label def date1ycontrol 288 "10/31/2012", modify
label def date1ycontrol 289 "11/11/2008", modify
label def date1ycontrol 290 "11/11/2013", modify
label def date1ycontrol 291 "11/11/2014", modify
label def date1ycontrol 292 "11/12/2009", modify
label def date1ycontrol 293 "11/12/2013", modify
label def date1ycontrol 294 "11/14/2006", modify
label def date1ycontrol 295 "11/15/2010", modify
label def date1ycontrol 296 "11/18/2014", modify
label def date1ycontrol 297 "11/19/2008", modify
label def date1ycontrol 298 "11/23/2007", modify
label def date1ycontrol 299 "11/25/2008", modify
label def date1ycontrol 300 "11/26/2010", modify
label def date1ycontrol 301 "11/26/2012", modify
label def date1ycontrol 302 "11/28/2006", modify
label def date1ycontrol 303 "11/28/2007", modify
label def date1ycontrol 304 "11/28/2010", modify
label def date1ycontrol 305 "12/12/2006", modify
label def date1ycontrol 306 "12/12/2007", modify
label def date1ycontrol 307 "12/12/2011", modify
label def date1ycontrol 308 "12/12/2013", modify
label def date1ycontrol 309 "12/13/2006", modify
label def date1ycontrol 310 "12/14/2007", modify
label def date1ycontrol 311 "12/14/2011", modify
label def date1ycontrol 312 "12/14/2012", modify
label def date1ycontrol 313 "12/15/2010", modify
label def date1ycontrol 314 "12/15/2014", modify
label def date1ycontrol 315 "12/16/2008", modify
label def date1ycontrol 316 "12/16/2010", modify
label def date1ycontrol 317 "12/16/2011", modify
label def date1ycontrol 318 "12/18/2009", modify
label def date1ycontrol 319 "12/18/2012", modify
label def date1ycontrol 320 "12/19/2008", modify
label def date1ycontrol 321 "12/20/2005", modify
label def date1ycontrol 322 "12/21/2010", modify
label def date1ycontrol 323 "12/22/2009", modify
label def date1ycontrol 324 "12/23/2008", modify
label def date1ycontrol 325 "9999", modify
[/CODE]

[Clyde Schechter]

-dataex- triummphs again! Your variable date1ycontrol has a value label (also called date1ycontrol) which overrides the display formatting you set.

But we also see you have a much bigger problem! Assuming that the dates you are dealing with are in fact in the 2000's, the values taken on by date1ycontrol are completely wrong! Most likely this variable was created by applying the -encode- command to a string variable that looks like dates in MM/DD/YYYY format. That gives you consecutive numbers from 1 through 324 (and 325 for your 9999 proxy for a missing value). But that's completely useless. What you actually have instead is a jumble of numbers that, if Stata were to calculate with them as date variables are all dates in 1960, and for which the numerical order does not correspond to the chronological order. (Notice, for example how the date corresponding to 49 is later than that corresponding to 50.)

The best solution would be to go back and revisit how this variable got created, and eliminate the -encode- command that created it, clean up the encoding of missing dates, and then proceed using:

replace that_string_variable =  if inlist(that_string_variable, "9999", ".")
gen date1ycontrol = daily(that_string_variable, "MDY")
format date1ycontrol %tdDDNNCCYY

Moreover, you should scrutinize that code for similar mishandling of other string variables. The -encode- command is only appropriate for use with string variables that represent nominal categories that have no actual numerical order (e.g. sex, religion, nationality). Any other string variable that human eyes read as numeric, and for which numeric operations or order comparisons would be meaningful, should be handled with -destring- or, in the case of date and time variables, -daily()- or some other suitable date function. You may well find other variables have been mishandled. Until you purge those errors, your analyses will produce useless and puzzling results that will be difficult to debug because the values look right when you -browse- or -list-, but they are very, very wrong.

If you do not actually have the code that created this, then an alternative solution is to undo the -encode-ing and work from there:

decode date1ycontrol, gen(string_date)
drop date1ycontrol
replace string_date =  if inlist(string_date, "9999", ".")
gen date1ycontrol = daily(string_date, "MDY")
format date1ycontrol %tdDDNNCCYY

And again, you need to scrutinize your data set for other similar problems. I would start with -ds, has(vallabel)-. Stata will respond with a list of all the variables that have value labels. Each of these should then be explored with -list-, and then -list, nolabel- so you can see whether the display versions (output of -list-) are obscuring nonsensical numerical coding (output of -list, nolabel-).

[Isabel Hostettler]

Dear Clyde,

I think it worked now although with the dataex command it looks strange:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float date1ycontrol
19403
19345
19701
19752
    .
    .
19472
19502
    .
19652
19491
    .
19674
19471
19539
19590
    .
    .
19593
19669
19598
19526
19631
19632
19565
    .
    .
19704
19808
19549
    .
19576
19673
19653
    .
    .
    .
19743
19730
    .
19753
    .
19649
    .
    .
    .
    .
    .
19792
19561
19794
19751
19807
19899
19822
    .
    .
19828
19229
    .
    .
19078
19222
19138
18965
19240
19102
    .
19281
19144
    .
19292
19137
19255
    .
19200
19297
19323
19291
    .
19408
    .
19254
19341
19411
19268
    .
    .
    .
19557
19540
19754
19522
19402
19409
    .
19269
19404
19399
    .
    .
19674
    .
19330
19423
    .
    .
    .
19561
19667
19423
19400
19234
19362
18673
18787
    .
18696
18774
    .
18759
18655
    .
    .
18799
18729
    .
18824
18876
18933
19036
18862
18876
18885
    .
18884
    .
    .
18896
18876
18855
18932
18920
    .
18918
    .
18885
    .
18977
    .
19109
18799
19061
    .
19079
    .
19046
18963
18975
    .
19064
    .
18973
19004
19275
18284
18280
18501
18421
18381
18404
18326
18350
18680
    .
18612
19267
    .
    .
18429
18435
    .
    .
18688
    .
18358
18605
18581
18518
18784
18521
    .
18655
    .
18542
    .
18540
18568
18478
18473
    .
18823
    .
19037
    .
18611
    .
19134
18527
18679
    .
18799
19366
18793
18592
18484
    .
    .
18674
    .
    .
19572
    .
18967
    .
19078
    .
18604
18723
    .
17924
    .
    .
18035
18003
    .
    .
    .
17967
18289
18105
    .
    .
    .
18197
18017
18249
18009
18029
18161
    .
    .
18043
18065
18311
18100
18126
    .
18071
18102
18154
18078
18150
18071
    .
18861
18501
18239
18239
18115
18203
18478
18666
    .
    .
    .
18337
18527
18365
    .
18213
18428
    .
18366
18694
18594
    .
18253
18343
    .
18617
    .
18742
    .
17874
17514
    .
    .
    .
    .
17686
    .
17538
    .
17541
    .
    .
17589
    .
    .
    .
17590
17591
    .
17623
17650
17659
    .
    .
17552
    .
17678
17624
    .
17625
18367
17667
17654
17711
17882
    .
17770
17702
    .
17727
17737
17744
17759
17769
18725
17912
17721
17808
17889
17779
17819
    .
17818
17815
17931
    .
17847
17855
17842
17861
17868
17925
    .
17873
    .
    .
    .
    .
    .
18071
18017
17885
    .
18032
17958
17512
17170
    .
17191
17196
17207
    .
    .
17470
    .
17283
17294
    .
17252
17261
17282
    .
    .
17246
17429
17304
17311
17437
17336
    .
    .
17357
    .
    .
    .
    .
17422
17478
    .
17364
17430
17462
17423
17419
17463
17458
    .
17465
    .
17800
    .
17493
    .
    .
17498
    .
17723
17514
17618
    .
    .
    .
17507
17552
16790
17266
    .
16861
17287
16870
16910
17070
    .
    .
16981
    .
    .
16856
    .
    .
16950
16945
16964
16947
17008
17003
    .
17260
    .
    .
    .
    .
    .
17070
    .
    .
17104
17114
    .
17087
    .
    .
17202
17317
17133
17119
    .
17148
17317
17203
17415
17176
17147
    .
    .
19963
    .
    .
    .
19907
    .
    .
    .
    .
20072
    .
    .
19761
19796
    .
20226
19842
20005
    .
    .
19836
    .
19876
    .
19866
    .
20006
19988
    .
20129
19932
    .
20038
20233
20093
    .
20030
    .
19974
    .
19939
    .
20005
    .
    .
    .
    .
20269
20045
20137
    .
20023
20024
    .
20107
    .
20012
    .
    .
20262
    .
20062
    .
    .
20206
    .
20059
    .
20048
end
format %tdDDNNCCYY date1ycontrol

------------------ copy up to and including the previous line ------------------



So what I've done is to import the original database as the variable then was imported as string variable. I've then applied as you suggested the commands:

gen date1ycontrol = daily(Date1ycontrol, "MDY")
format date1ycontrol %tdDDNNCCYY.

The dates look good in the browser just the dataex looks strange to me but maybe that's only because I'm not used to it.

Kind regards,

Isabel

[Nick Cox]

Nothing obviously wrong now: it all looks good. Handling dates in Stata requires understanding that

1. Stata uses the beginning of 1960 as an origin. That is arbitrary, but so would any other origin be.

2. To think beyond difficult-to-think-about dates like 20048 Stata provides date display formats so that you can see 21 Nov 2014, or whatever. .

See the help on dates as a high priority as you are still unclear on this, Many people get this wrong, but really it is because they didn't study the documentation carefully enough, or even at all.

The thread still leaves open why your date variable was ever put through encode in the first place. If some person or text advised you to do that, you have learned the hard way that it is utterly wrong.

[Isabel Hostettler]

Dear Nick,

yes, it's the next thing I really have to read and learn. I took the dataset over from someone that's why I didn't realise in the beginning what the problem was as previously it didn't happen.

I'm very grateful that it's correct now. Thank you for your help and suggestions from improving.

Kind regards,

Isabel