Dear Statalisters:
I am new to Stata and new to databases. I've been trying to get an access database "Stata ready". I can insheet it well and have "cleaned up" most of the variables. However, I've hit a snag with one variable named "Ferritin", reproduced below for those interested. (n= 362).
My goal is to separate the value form the date.
But note:
1. Most have a four digit value
2. Many have a date of the test in an inconsistent format
3. Many have "U" for unknown
4. One has "normal" for a value
5. A few have the ">" operator before the value
6. One has the units for the measurement
I've tried the date() functions, split, and word() and all cause some other problem.
This is important because the ferritin is the first of a list of similarly (mis)coded variables.
Is there a Stata-esque approach to this, or am I left with line-by-line cleanup?
Second question is whether ASSERT can be used to test whether a value is a date, integer, string, etc?
Many thanks.
. l ferritin
+-------------------+
| ferritin |
|-------------------|
1. | 540 11/6/02 |
2. | U |
3. | 29 10/17/01 |
4. | 365 1/17/01 |
5. | U |
|-------------------|
6. | 265 1/22/03 |
7. | 432 2/3/03 |
8. | U |
9. | 3899 12/9/02 |
10. | U |
|-------------------|
11. | U |
12. | U |
13. | 85 12/8/99 |
14. | U |
15. | U |
|-------------------|
16. | U |
17. | U |
18. | U |
19. | U |
20. | 1013 5/11/01 |
|-------------------|
21. | U |
22. | U |
23. | 1292 2/24/03 |
24. | U |
25. | 103 10/18/99 |
|-------------------|
26. | U |
27. | U |
28. | 3136 6/16/99 |
29. | 931 8/22/01 |
30. | U |
|-------------------|
31. | U |
32. | U |
33. | 219 6/3/02 |
34. | U |
35. | 1454 10/8/02 |
|-------------------|
36. | U |
37. | U |
38. | U |
39. | U |
40. | U |
|-------------------|
41. | U |
42. | 1971 5/18/03 |
43. | 29 5/13/03 |
44. | 37 2/21/00 |
45. | U |
|-------------------|
46. | 823 1/10/03 |
47. | 392 4/14/03 |
48. | 1120 7/7/03 |
49. | U |
50. | 373 10/23/02 |
|-------------------|
51. | 390 2/14/03 |
52. | 122 10/3/01 |
53. | 3639 3/14/01 |
54. | 111 6/10/02 |
55. | U |
|-------------------|
56. | 1138 7/31/02 |
57. | 109 3/28/01 |
58. | U |
59. | 415 8/22/03 |
60. | U |
|-------------------|
61. | U |
62. | U |
63. | U |
64. | 94 11/24/01 |
65. | 8494 9/9/02 |
|-------------------|
66. | 5760 5/02 |
67. | 418 11/27/00 |
68. | 279 3/31/03 |
69. | 3075 1/5/03 |
70. | 4968 |
|-------------------|
71. | 1009 12/12/02 |
72. | U |
73. | 3605 8/2/03 |
74. | 990 8/7/02 |
75. | 102 12/17/01 |
|-------------------|
76. | 819 11/8/02 |
77. | 1060 7/24/00 |
78. | 1201 4/19/01 |
79. | 484 3/1/02 |
80. | 4366 1/18/02 |
|-------------------|
81. | 9 5/21/03 |
82. | 2245 12/13/02 |
83. | U |
84. | 1198 8/1/03 |
85. | 5775 7/6/03 |
|-------------------|
86. | 315 7/10/03 |
87. | U |
88. | U |
89. | 3951 8/15/01 |
90. | U |
|-------------------|
91. | 739 1/20/99 |
92. | 68 9/30/02 |
93. | 3433 12/10/02 |
94. | 4096 9/25/02 |
95. | 848 6/23/02 |
|-------------------|
96. | U |
97. | 1611 8/17/03 |
98. | 4782 6/02 |
99. | 1712 2/5/01 |
100. | 183 7/15/02 |
|-------------------|
101. | 199 3/1/01 |
102. | U |
103. | 4937 10/6/02 |
104. | 303 7/25/02 |
105. | U |
|-------------------|
106. | 3048 3/20/03 |
107. | 2147 2/4/02 |
108. | 1772 6/4/02 |
109. | 722 7/20/99 |
110. | 17 4/28/03 |
|-------------------|
111. | U |
112. | 862 12/11/00 |
113. | U |
114. | U |
115. | 589 1/8/01 |
|-------------------|
116. | U |
117. | 1137 4/02 |
118. | 4339 7/22/03 |
119. | U |
120. | 134 9/9/02 |
|-------------------|
121. | 3237 3/01 |
122. | 11832 4/14/03 |
123. | 685 8/23/00 |
124. | 948 2/1/99 |
125. | 224 12/13/00 |
|-------------------|
126. | 1060 8/02 |
127. | U |
128. | 1178 7/17/02 |
129. | 7853 11/25/02 |
130. | 1791 12/4/02 |
|-------------------|
131. | U |
132. | 189 10/6/02 |
133. | 5290 12/14/02 |
134. | 46 2/7/01 |
135. | 2661 1/10/01 |
|-------------------|
136. | 450 2/21/03 |
137. | 1452 2/13/03 |
138. | 31 1/22/03 |
139. | 744 5/2/01 |
140. | U |
|-------------------|
141. | U |
142. | U |
143. | 146 2/12/01 |
144. | U |
145. | 3948 11/12/02 |
|-------------------|
146. | 911 9/18/02 |
147. | 159 8/29/02 |
148. | 1422 ng/ml 3/1998 |
149. | U |
150. | 519 10/18/02 |
|-------------------|
151. | 35 4/9/01 |
152. | 120 3/15/00 |
153. | U |
154. | 1014 8/01 |
155. | 453 3/3/01 |
|-------------------|
156. | U |
157. | 889 5/2/01 |
158. | 60 3/28/01 |
159. | 52 4/18/01 |
160. | 151 7/19/02 |
|-------------------|
161. | U |
162. | 115 6/12/02 |
163. | 175 11/13/00 |
164. | U |
165. | 23 8/30/00 |
|-------------------|
166. | 118 10/20/02 |
167. | 994 1/27/03 |
168. | 1502 4/2/01 |
169. | U |
170. | U |
|-------------------|
171. | 947 1/24/01 |
172. | 6773 2/11/03 |
173. | 1649 3/12/02 |
174. | U |
175. | 1490 2/17/03 |
|-------------------|
176. | 11241 9/4/02 |
177. | U |
178. | normal |
179. | 3431 1/24/03 |
180. | 540 11/6/02 |
|-------------------|
181. | 2132 12/18/00 |
182. | U |
183. | U |
184. | 2549 3/31/03 |
185. | U |
|-------------------|
186. | U |
187. | 2088 2/10/03 |
188. | 653 12/15/02 |
189. | 58 2/18/03 |
190. | U |
|-------------------|
191. | U |
192. | 51 1/4/02 |
193. | U |
194. | U |
195. | 3910 1/7/02 |
|-------------------|
196. | U |
197. | 4067 1/12/03 |
198. | U |
199. | 3790 2/24/03 |
200. | 33 2/14/01 |
|-------------------|
201. | 2170 2/2/03 |
202. | 3610 2/4/03 |
203. | 266 11/6/02 |
204. | 92 8/4/99 |
205. | 86 2/5/01 |
|-------------------|
206. | 4100 4/22/03 |
207. | 1862 9/26/00 |
208. | U |
209. | 646 mg/ml 9/2000 |
210. | 1938 |
|-------------------|
211. | 1716 2/12/03 |
212. | 127 |
213. | 973 7/23/01 |
214. | 40 3/5/01 |
215. | U |
|-------------------|
216. | U |
217. | 708 12/6/01 |
218. | U |
219. | U |
220. | 725 3/17/00 |
|-------------------|
221. | 388 3/19/03 |
222. | 2582 1/15/03 |
223. | U |
224. | 1149 4/28/03 |
225. | |
|-------------------|
226. | 1872 1/26/03 |
227. | 386 9/5/01 |
228. | U |
229. | 1733 3/23/03 |
230. | 466 10/17/02 |
|-------------------|
231. | 5227 7/17/02 |
232. | 1311 12/21/01 |
233. | U |
234. | 781 3/15/00 |
235. | 858 7/27/01 |
|-------------------|
236. | U |
237. | U |
238. | U |
239. | 64 4/3/03 |
240. | U |
|-------------------|
241. | 2158 4/9/03 |
242. | 1808 8/7/02 |
243. | U |
244. | 2216 2/11/03 |
245. | 458 4/1/03 |
|-------------------|
246. | 2807 11/6/02 |
247. | U |
248. | U |
249. | U |
250. | U |
|-------------------|
251. | U |
252. | 681 1/5/03 |
253. | U |
254. | U |
255. | U |
|-------------------|
256. | U |
257. | 258 4/18/01 |
258. | 47 6/7/01 |
259. | 128 2/10/03 |
260. | 6011 12/8/02 |
|-------------------|
261. | 58 4/12/02 |
262. | 1319 9/20/00 |
263. | 3990 8/5/02 |
264. | U |
265. | U |
|-------------------|
266. | U |
267. | 2080 8/15/01 |
268. | 6967 1/8/03 |
269. | U |
270. | 1026 3/20/00 |
|-------------------|
271. | U |
272. | 36 5/18/01 |
273. | 5934 9/13/99 |
274. | U |
275. | 1448 10/11/02 |
|-------------------|
276. | 455 1/10/00 |
277. | U |
278. | 129 6/00 |
279. | U |
280. | U |
|-------------------|
281. | U |
282. | U |
283. | U |
284. | U |
285. | U |
|-------------------|
286. | >1400 3/24/99 |
287. | 191 5/22/01 |
288. | 15012 8/24/02 |
289. | 2000 |
290. | U |
|-------------------|
291. | 24 6/5/02 |
292. | U |
293. | 127 11/19/01 |
294. | 7779 9/26/01 |
295. | 99 11/07/01 |
|-------------------|
296. | 584 7/29/00 |
297. | 3700 12/3/03 |
298. | 670 3/23/02 |
299. | 1814 9/21/02 |
300. | 4995 |
|-------------------|
301. | 2748 9/02 |
302. | 187 3/2000 |
303. | 489 |
304. | 4974 6/6/01 |
305. | 3564 11/01 |
|-------------------|
306. | 104 4/20/01 |
307. | 862 1990 |
308. | U |
309. | 1458 8/12/02 |
310. | 73 10/24/02 |
|-------------------|
311. | U |
312. | 24 11/17/02 |
313. | 4305 7/22/01 |
314. | U |
315. | 1630 3/5/01 |
|-------------------|
316. | U |
317. | 2217 9/6/02 |
318. | 140 1/4/01 |
319. | 3621 12/02/02 |
320. | 416 7/31/02 |
|-------------------|
321. | U |
322. | 59 12/6/00 |
323. | U |
324. | > 2K 1/7/02 |
325. | 45 11/16/01 |
|-------------------|
326. | 92 2/21/01 |
327. | U |
328. | U |
329. | 244 3/5/01 |
330. | U |
|-------------------|
331. | U |
332. | 173 4/22/02 |
333. | 3039 2/02 |
334. | U |
335. | 120 12/5/01 |
|-------------------|
336. | U |
337. | 332 3/26/01 |
338. | 1777 ng/ml 5/02 |
339. | U |
340. | U |
|-------------------|
341. | U |
342. | 314 5/25/01 |
343. | U |
344. | 861 11/11/02 |
345. | U |
|-------------------|
346. | 2631 10/11/02 |
347. | U |
348. | U |
349. | U |
350. | 132 2/20/02 |
|-------------------|
351. | U |
352. | 40 3/02 |
353. | 97 3/29/02 |
354. | 6151 5/22/02 |
355. | U |
|-------------------|
356. | 1390 5/8/02 |
357. | 953 |
358. | 54 8/2/02 |
359. | 2779 8/12/02 |
360. | U |
|-------------------|
361. | U |
362. | U |
+-------------------+
.
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
