topic Re: Invalid data error in SAS Programming

Invalid data error

sks521 — Tue, 30 Jul 2019 09:30:46 GMT

Hi folks,

I am importing a data set using proc import as below;

{

proc import datafile = 'I:\cYorkshire Project\Care pathway for asthma\Sarwat\Data\Datasets\2019-04-01-ace\ACE_AE_20190401.csv'
out = AandE
dbms = csv;
run;

}

However get errors in multiple places, Please can someone see the log and tell me where has it gone wrong;

{

5 proc import datafile = 'I:\cYorkshire Project\Care pathway for
5 ! asthma\Sarwat\Data\Datasets\2019-04-01-ace\ACE_AE_20190401.csv'
6 out = AandE
7 dbms = csv;
8 run;

9 /**********************************************************************
10 * PRODUCT: SAS
11 * VERSION: 9.4
12 * CREATOR: External File Interface
13 * DATE: 29JUL19
14 * DESC: Generated SAS Datastep Code
15 * TEMPLATE SOURCE: (None Specified.)
16 ***********************************************************************/
17 data WORK.AANDE ;
18 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
19 infile 'I:\cYorkshire Project\Care pathway for
19 ! asthma\Sarwat\Data\Datasets\2019-04-01-ace\ACE_AE_20190401.csv' delimiter = ',' MISSOVER DSD
19 ! lrecl=32767 firstobs=2 ;
20 informat aekey $32. ;
21 informat patient_id $32. ;
22 informat link_method $18. ;
23 informat procodet $3. ;
24 informat postcode_flag $5. ;
25 informat sex best32. ;
26 informat ethnos $1. ;
27 informat activage $3. ;
28 informat arrivalage best32. ;
29 informat pgpprac $32. ;
30 informat sitetret $5. ;
31 informat aearrivalmode best32. ;
32 informat aeattenddisp best32. ;
33 informat aerefsource best32. ;
34 informat aedepttype best32. ;
35 informat aearrivaldate yymmdd10. ;
36 informat aearrivaltime best32. ;
37 informat diag_scheme best32. ;
38 informat diag_01 $6. ;
39 informat diag_02 $2. ;
40 informat diag_03 $2. ;
41 informat diag_04 $2. ;
42 informat diag_05 $2. ;
43 informat diag_06 $2. ;
44 informat diag_07 $2. ;
45 informat diag_08 $2. ;
46 informat diag_09 $2. ;
47 informat diag_10 $2. ;
48 informat diag_11 $2. ;
49 informat diag_12 $2. ;
50 informat invest_01 best32. ;
51 informat invest_02 $2. ;
52 informat invest_03 $2. ;
53 informat invest_04 $2. ;
54 informat invest_05 $2. ;
55 informat invest_06 $2. ;
56 informat invest_07 $2. ;
57 informat invest_08 $2. ;
58 informat invest_09 $2. ;
59 informat invest_10 $2. ;
60 informat invest_11 $2. ;
61 informat invest_12 $2. ;
62 informat treat_01 best32. ;
63 informat treat_02 $3. ;
64 informat treat_03 $3. ;
65 informat treat_04 $3. ;
66 informat treat_05 $2. ;
67 informat treat_06 $2. ;
68 informat treat_07 $2. ;
69 informat treat_08 $2. ;
70 informat treat_09 $2. ;
71 informat treat_10 $2. ;
72 informat treat_11 $2. ;
73 informat treat_12 $2. ;
74 informat imd15decile_overall best32. ;
75 format aekey $32. ;
76 format patient_id $32. ;
77 format link_method $18. ;
78 format procodet $3. ;
79 format postcode_flag $5. ;
80 format sex best12. ;
81 format ethnos $1. ;
82 format activage $3. ;
83 format arrivalage best12. ;
84 format pgpprac $32. ;
85 format sitetret $5. ;
86 format aearrivalmode best12. ;
87 format aeattenddisp best12. ;
88 format aerefsource best12. ;
89 format aedepttype best12. ;
90 format aearrivaldate yymmdd10. ;
91 format aearrivaltime best12. ;
92 format diag_scheme best12. ;
93 format diag_01 $6. ;
94 format diag_02 $2. ;
95 format diag_03 $2. ;
96 format diag_04 $2. ;
97 format diag_05 $2. ;
98 format diag_06 $2. ;
99 format diag_07 $2. ;
100 format diag_08 $2. ;
101 format diag_09 $2. ;
102 format diag_10 $2. ;
103 format diag_11 $2. ;
104 format diag_12 $2. ;
105 format invest_01 best12. ;
106 format invest_02 $2. ;
107 format invest_03 $2. ;
108 format invest_04 $2. ;
109 format invest_05 $2. ;
110 format invest_06 $2. ;
111 format invest_07 $2. ;
112 format invest_08 $2. ;
113 format invest_09 $2. ;
114 format invest_10 $2. ;
115 format invest_11 $2. ;
116 format invest_12 $2. ;
117 format treat_01 best12. ;
118 format treat_02 $3. ;
119 format treat_03 $3. ;
120 format treat_04 $3. ;
121 format treat_05 $2. ;
122 format treat_06 $2. ;
123 format treat_07 $2. ;
124 format treat_08 $2. ;
125 format treat_09 $2. ;
126 format treat_10 $2. ;
127 format treat_11 $2. ;
128 format treat_12 $2. ;
129 format imd15decile_overall best12. ;
130 input
131 aekey $
132 patient_id $
133 link_method $
134 procodet $
135 postcode_flag $
136 sex
137 ethnos $
138 activage $
139 arrivalage
140 pgpprac $
141 sitetret $
142 aearrivalmode
143 aeattenddisp
144 aerefsource
145 aedepttype
146 aearrivaldate
147 aearrivaltime
148 diag_scheme
149 diag_01 $
150 diag_02 $
151 diag_03 $
152 diag_04 $
153 diag_05 $
154 diag_06 $
155 diag_07 $
156 diag_08 $
157 diag_09 $
158 diag_10 $
159 diag_11 $
160 diag_12 $
161 invest_01
162 invest_02 $
163 invest_03 $
164 invest_04 $
165 invest_05 $
166 invest_06 $
167 invest_07 $
168 invest_08 $
169 invest_09 $
170 invest_10 $
171 invest_11 $
172 invest_12 $
173 treat_01
174 treat_02 $
175 treat_03 $
176 treat_04 $
177 treat_05 $
178 treat_06 $
179 treat_07 $
180 treat_08 $
181 treat_09 $
182 treat_10 $
183 treat_11 $
184 treat_12 $
185 imd15decile_overall
186 ;
187 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
188 run;

NOTE: The infile 'I:\cYorkshire Project\Care pathway for
asthma\Sarwat\Data\Datasets\2019-04-01-ace\ACE_AE_20190401.csv' is:

Filename=I:\cYorkshire Project\Care pathway for
asthma\Sarwat\Data\Datasets\2019-04-01-ace\ACE_AE_20190401.csv,
RECFM=V,LRECL=32767,
File Size (bytes)=420517409,
Last Modified=01 April 2019 18:58:31,
Create Time=01 April 2019 18:37:33

NOTE: Invalid data for aerefsource in line 38 135-136.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
38 f537f23f13d4316a5085f35ea469c8b2,699666344ed513a15dc745eabf1deb3b,NHSNO,RXF,VALID,2,A,0
88 05,72,2a39f9120297aeaaca766b1483bcbba8,NA,2,02,NA,01,2013-06-26,1204,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,221,222,NA,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,2 270
aekey=f537f23f13d4316a5085f35ea469c8b2 patient_id=699666344ed513a15dc745eabf1deb3b
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2 ethnos=A activage=005 arrivalage=72
pgpprac=2a39f9120297aeaaca766b1483bcbba8 sitetret=NA aearrivalmode=2 aeattenddisp=2 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-26 aearrivaltime=1204 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=24 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=221 treat_02=222 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=2 _ERROR_=1 _N_=37
NOTE: Invalid data for aerefsource in line 100 135-136.
100 83a15549b5951c8770e10bdbeea128df,ab340d8e605d3b38bedf2da2bfdbf35c,NHSNO,RXF,VALID,1,A,0
88 08,52,3d32f458f031f4fe258f2b492b3f803c,NA,2,02,NA,01,2013-06-26,0944,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,1 269
aekey=83a15549b5951c8770e10bdbeea128df patient_id=ab340d8e605d3b38bedf2da2bfdbf35c
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1 ethnos=A activage=008 arrivalage=52
pgpprac=3d32f458f031f4fe258f2b492b3f803c sitetret=NA aearrivalmode=2 aeattenddisp=2 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-26 aearrivaltime=944 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=24 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=1 _ERROR_=1 _N_=99
NOTE: Invalid data for aerefsource in line 149 135-136.
149 6194f1a398d6b7c068f84b3add799438,7d678f09ca422e57586a83f621453ff8,NHSNO,RXF,VALID,2,A,0
88 03,47,a9658493c4ad50ca809e1bcda4570971,NA,2,03,NA,01,2013-06-25,1040,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,3 269
aekey=6194f1a398d6b7c068f84b3add799438 patient_id=7d678f09ca422e57586a83f621453ff8
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2 ethnos=A activage=003 arrivalage=47
pgpprac=a9658493c4ad50ca809e1bcda4570971 sitetret=NA aearrivalmode=2 aeattenddisp=3 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-25 aearrivaltime=1040 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=24 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=3 _ERROR_=1 _N_=148
NOTE: Invalid data for aerefsource in line 162 135-136.
162 3371615f5da68e1514e3446eeb5b5102,619e54142e4b370ef353850bc15a3ef7,NHSNO,RXF,VALID,1,J,0
88 12,50,e8f660dbeddf6aa38c09ffc952b8a022,NA,2,03,NA,01,2013-06-24,1125,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,2 269
aekey=3371615f5da68e1514e3446eeb5b5102 patient_id=619e54142e4b370ef353850bc15a3ef7
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1 ethnos=J activage=012 arrivalage=50
pgpprac=e8f660dbeddf6aa38c09ffc952b8a022 sitetret=NA aearrivalmode=2 aeattenddisp=3 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-24 aearrivaltime=1125 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=24 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=2 _ERROR_=1 _N_=161
NOTE: Invalid data for aerefsource in line 173 135-136.
173 4fa6c462785090999ee53dda8e790f96,6705911a99c93a09ef5e605e8d9ced3b,NHSNO,RXF,VALID,1,A,0
88 10,50,6cd8a882376104fa1a7db8a66185bdda,NA,2,03,NA,01,2013-06-10,1020,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,7 269
aekey=4fa6c462785090999ee53dda8e790f96 patient_id=6705911a99c93a09ef5e605e8d9ced3b
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1 ethnos=A activage=010 arrivalage=50
pgpprac=6cd8a882376104fa1a7db8a66185bdda sitetret=NA aearrivalmode=2 aeattenddisp=3 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-10 aearrivaltime=1020 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=24 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=7 _ERROR_=1 _N_=172
NOTE: Invalid data for aerefsource in line 193 135-136.
193 dce87811af7410d8da414f4ac6e526cc,c6f1650922f49dadaeafa32b1aa44ec8,NHSNO,RXF,VALID,1,A,0
88 06,51,e7c400b0c9b7de3d6465d4525908c955,NA,2,02,NA,01,2013-06-03,1043,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,06,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,7 269
aekey=dce87811af7410d8da414f4ac6e526cc patient_id=c6f1650922f49dadaeafa32b1aa44ec8
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1 ethnos=A activage=006 arrivalage=51
pgpprac=e7c400b0c9b7de3d6465d4525908c955 sitetret=NA aearrivalmode=2 aeattenddisp=2 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-03 aearrivaltime=1043 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=24 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=6 treat_02=222 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=7 _ERROR_=1 _N_=192
NOTE: Invalid data for aerefsource in line 217 135-136.
217 9259308a816651f1653decb5c8356120,c2ccb86eb8388c2e2ba60493c7644f45,NHSNO,RXF,VALID,2,J,0
88 12,51,51c5f674fc8d4674054fc56574548648,NA,2,02,NA,01,2013-06-07,0916,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,7 269
aekey=9259308a816651f1653decb5c8356120 patient_id=c2ccb86eb8388c2e2ba60493c7644f45
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2 ethnos=J activage=012 arrivalage=51
pgpprac=51c5f674fc8d4674054fc56574548648 sitetret=NA aearrivalmode=2 aeattenddisp=2 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-07 aearrivaltime=916 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=24 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=7 _ERROR_=1 _N_=216
NOTE: Invalid data for aerefsource in line 246 135-136.
246 a4cb4e4db32fd7185ea8e1ff02fa9e1f,5d05514d5b99ae9e48613898e27bfff0,NHSNO,RXF,VALID,1,A,0
88 14,54,9154441d92dae912d6da058c3c248436,NA,2,03,NA,01,2013-06-11,1006,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,10,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,10 270
aekey=a4cb4e4db32fd7185ea8e1ff02fa9e1f patient_id=5d05514d5b99ae9e48613898e27bfff0
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1 ethnos=A activage=014 arrivalage=54
pgpprac=9154441d92dae912d6da058c3c248436 sitetret=NA aearrivalmode=2 aeattenddisp=3 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-11 aearrivaltime=1006 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=10 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=10 _ERROR_=1 _N_=245
NOTE: Invalid data for aerefsource in line 275 135-136.
275 d4ef30652024954e940430e6c8168173,b61526b89a27cb586976500c94cf3838,NHSNO,RXF,VALID,2,A,0
88 16,48,a9658493c4ad50ca809e1bcda4570971,NA,2,02,NA,01,2013-06-07,0904,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,01,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,06,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,1 269
aekey=d4ef30652024954e940430e6c8168173 patient_id=b61526b89a27cb586976500c94cf3838
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2 ethnos=A activage=016 arrivalage=48
pgpprac=a9658493c4ad50ca809e1bcda4570971 sitetret=NA aearrivalmode=2 aeattenddisp=2 aerefsource=.
aedepttype=1 aearrivaldate=2013-06-07 aearrivaltime=904 diag_scheme=1 diag_01=NA diag_02=NA
diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA diag_10=NA
diag_11=NA diag_12=NA invest_01=1 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=6 treat_02=222 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=1 _ERROR_=1 _N_=274
NOTE: Invalid data for invest_01 in line 292 196-197.
NOTE: Invalid data for treat_01 in line 292 232-233.
292 5b29a19dc3bce9ad01c65fa6b725cf98,9deb2b941fbcf8b3cb425188b39ba5c2,NHSNO,RXF,VALID,1,A,0
88 16,16,d8febc1b8d18e84e87b5a5d515492b50,NA,2,12,07,01,2012-05-21,2032,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,
262 NA,NA,5 268
aekey=5b29a19dc3bce9ad01c65fa6b725cf98 patient_id=9deb2b941fbcf8b3cb425188b39ba5c2
link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1 ethnos=A activage=016 arrivalage=16
pgpprac=d8febc1b8d18e84e87b5a5d515492b50 sitetret=NA aearrivalmode=2 aeattenddisp=12
aerefsource=7 aedepttype=1 aearrivaldate=2012-05-21 aearrivaltime=2032 diag_scheme=1 diag_01=NA
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=. invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=. treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=5 _ERROR_=1 _N_=291
NOTE: Invalid data for invest_01 in line 294 207-208.
294 8ea3531e28cd15997630c87db8359a21,3bd48a3d471d3154e11c5852d86397df,NHSNO,RFF,VALID,1,A,0
88 16,16,6fab353bff374f8a7f7dc7090941fe0a,RFFAA,2,03,08,01,2012-06-14,1509,02,S602,S509,S3
175 01,S009,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,30,222,511,NA,NA,NA
262 ,NA,NA,NA,NA,NA,NA,1 281
aekey=8ea3531e28cd15997630c87db8359a21 patient_id=3bd48a3d471d3154e11c5852d86397df
link_method=NHSNO procodet=RFF postcode_flag=VALID sex=1 ethnos=A activage=016 arrivalage=16
pgpprac=6fab353bff374f8a7f7dc7090941fe0a sitetret=RFFAA aearrivalmode=2 aeattenddisp=3
aerefsource=8 aedepttype=1 aearrivaldate=2012-06-14 aearrivaltime=1509 diag_scheme=2 diag_01=S602
diag_02=S5 diag_03=S3 diag_04=S0 diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=. invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=30 treat_02=222 treat_03=511 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=1 _ERROR_=1 _N_=293
NOTE: Invalid data for aearrivalmode in line 307 133-134.
307 722d022f65999189432e699c299c27ea,619cff04338a21126ffe2f74a23129d8,NHSNO,RCD,VALID,1,A,0
88 16,16,12fab583c6f6be2a755ed9882e6a369f,RCD00,NA,03,03,01,2012-08-27,0852,01,NA,NA,NA,NA
175 ,NA,NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,NA,8 273
aekey=722d022f65999189432e699c299c27ea patient_id=619cff04338a21126ffe2f74a23129d8
link_method=NHSNO procodet=RCD postcode_flag=VALID sex=1 ethnos=A activage=016 arrivalage=16
pgpprac=12fab583c6f6be2a755ed9882e6a369f sitetret=RCD00 aearrivalmode=. aeattenddisp=3
aerefsource=3 aedepttype=1 aearrivaldate=2012-08-27 aearrivaltime=852 diag_scheme=1 diag_01=NA
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=24 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=8 _ERROR_=1 _N_=306
NOTE: Invalid data for imd15decile_overall in line 329 271-272.
329 f7e0df9fb33b00d8911876e2edc57d73,e19d0e7c8ba2e2fd06958db169e212dd,NHSNO,RR8,VALID,2,A,0
88 16,16,411b928ccd78d70c4e9685533306c7b3,RR801,2,02,01,01,2012-03-18,1907,01,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,30,57,NA,NA,NA,NA,NA,NA,NA,
262 NA,NA,NA,NA 272
aekey=f7e0df9fb33b00d8911876e2edc57d73 patient_id=e19d0e7c8ba2e2fd06958db169e212dd
link_method=NHSNO procodet=RR8 postcode_flag=VALID sex=2 ethnos=A activage=016 arrivalage=16
pgpprac=411b928ccd78d70c4e9685533306c7b3 sitetret=RR801 aearrivalmode=2 aeattenddisp=2
aerefsource=1 aedepttype=1 aearrivaldate=2012-03-18 aearrivaltime=1907 diag_scheme=1 diag_01=NA
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=24 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=30 treat_02=57 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=328
NOTE: Invalid data for treat_01 in line 360 237-238.
360 01f56d7e49c37b5daca14fa4e99418f3,dab8d827b2b9d1bdd69758f40674c462,NHSNO,RAE,VALID,1,A,0
88 16,16,f9d800036dca88a9532e81c75335471d,RAE01,2,05,01,01,2012-01-11,1101,02,M799,NA,NA,N
175 A,NA,NA,NA,NA,NA,NA,NA,NA,01,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,NA,5 273
aekey=01f56d7e49c37b5daca14fa4e99418f3 patient_id=dab8d827b2b9d1bdd69758f40674c462
link_method=NHSNO procodet=RAE postcode_flag=VALID sex=1 ethnos=A activage=016 arrivalage=16
pgpprac=f9d800036dca88a9532e81c75335471d sitetret=RAE01 aearrivalmode=2 aeattenddisp=5
aerefsource=1 aedepttype=1 aearrivaldate=2012-01-11 aearrivaltime=1101 diag_scheme=2 diag_01=M799
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=1 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=. treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=5 _ERROR_=1 _N_=359
NOTE: Invalid data for treat_01 in line 362 237-238.
362 63b2a80c74f85fe5f4c3f28915f2d4c9,dab8d827b2b9d1bdd69758f40674c462,NHSNO,RAE,VALID,1,A,0
88 16,16,f9d800036dca88a9532e81c75335471d,RAE01,2,03,06,01,2012-10-13,2017,02,T143,NA,NA,N
175 A,NA,NA,NA,NA,NA,NA,NA,NA,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,NA,1 273
aekey=63b2a80c74f85fe5f4c3f28915f2d4c9 patient_id=dab8d827b2b9d1bdd69758f40674c462
link_method=NHSNO procodet=RAE postcode_flag=VALID sex=1 ethnos=A activage=016 arrivalage=16
pgpprac=f9d800036dca88a9532e81c75335471d sitetret=RAE01 aearrivalmode=2 aeattenddisp=3
aerefsource=6 aedepttype=1 aearrivaldate=2012-10-13 aearrivaltime=2017 diag_scheme=2 diag_01=T143
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=1 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=. treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=1 _ERROR_=1 _N_=361
NOTE: Invalid data for treat_01 in line 374 239-240.
374 3a75e19d74ba98f5f6abf5ebb32aa0dc,4581b104a6647a12eca2bd8b022fcc2f,NHSNO,RAE,VALID,1,A,0
88 16,16,40d0a59e26967dbe78ef35e498be2827,RAE01,2,01,01,01,2016-03-04,0857,02,T141,L089,NA
175 ,NA,NA,NA,NA,NA,NA,NA,NA,NA,03,05,14,04,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,NA,NA,7 275
aekey=3a75e19d74ba98f5f6abf5ebb32aa0dc patient_id=4581b104a6647a12eca2bd8b022fcc2f
link_method=NHSNO procodet=RAE postcode_flag=VALID sex=1 ethnos=A activage=016 arrivalage=16
pgpprac=40d0a59e26967dbe78ef35e498be2827 sitetret=RAE01 aearrivalmode=2 aeattenddisp=1
aerefsource=1 aedepttype=1 aearrivaldate=2016-03-04 aearrivaltime=857 diag_scheme=2 diag_01=T141
diag_02=L0 diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=3 invest_02=05 invest_03=14 invest_04=04 invest_05=01
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=. treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=7 _ERROR_=1 _N_=373
NOTE: Invalid data for aerefsource in line 400 139-140.
400 99a76aa7359fc351f6abb3b284821ad1,ede98159664225badf6f74d19546546c,NHSNO,RFR,VALID,1,99,
88 015,14,aebb64d34bef243560e5b04a8cc51656,RFRPA,2,03,NA,01,2012-07-12,1937,01,NA,NA,NA,NA
175 ,NA,NA,NA,NA,NA,NA,NA,NA,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,NA,3 273
aekey=99a76aa7359fc351f6abb3b284821ad1 patient_id=ede98159664225badf6f74d19546546c
link_method=NHSNO procodet=RFR postcode_flag=VALID sex=1 ethnos=9 activage=015 arrivalage=14
pgpprac=aebb64d34bef243560e5b04a8cc51656 sitetret=RFRPA aearrivalmode=2 aeattenddisp=3
aerefsource=. aedepttype=1 aearrivaldate=2012-07-12 aearrivaltime=1937 diag_scheme=1 diag_01=NA
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=1 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=3 _ERROR_=1 _N_=399
NOTE: Invalid data for aearrivalmode in line 403 133-134.
403 5b20cb5207efac16c346e0f238ac25f0,473935e03eb2861b9dcabbdd645e46c2,NHSNO,RCD,VALID,2,A,0
88 13,13,07c123a28f5a5dfb633db7007b1446ee,RCD00,NA,05,08,01,2012-11-23,0859,01,NA,NA,NA,NA
175 ,NA,NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,53,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,NA,7 272
aekey=5b20cb5207efac16c346e0f238ac25f0 patient_id=473935e03eb2861b9dcabbdd645e46c2
link_method=NHSNO procodet=RCD postcode_flag=VALID sex=2 ethnos=A activage=013 arrivalage=13
pgpprac=07c123a28f5a5dfb633db7007b1446ee sitetret=RCD00 aearrivalmode=. aeattenddisp=5
aerefsource=8 aedepttype=1 aearrivaldate=2012-11-23 aearrivaltime=859 diag_scheme=1 diag_01=NA
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=24 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=53 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=7 _ERROR_=1 _N_=402
NOTE: Invalid data for aearrivalmode in line 405 133-134.
NOTE: Invalid data for aerefsource in line 405 139-140.
405 fc0532e8911629c0719ddbe61ca32cba,473935e03eb2861b9dcabbdd645e46c2,NHSNO,RCD,VALID,2,A,0
88 15,15,07c123a28f5a5dfb633db7007b1446ee,RCD00,NA,03,NA,01,2014-09-22,1727,01,NA,NA,NA,NA
175 ,NA,NA,NA,NA,NA,NA,NA,NA,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,NA,7 273
aekey=fc0532e8911629c0719ddbe61ca32cba patient_id=473935e03eb2861b9dcabbdd645e46c2
link_method=NHSNO procodet=RCD postcode_flag=VALID sex=2 ethnos=A activage=015 arrivalage=15
pgpprac=07c123a28f5a5dfb633db7007b1446ee sitetret=RCD00 aearrivalmode=. aeattenddisp=3
aerefsource=. aedepttype=1 aearrivaldate=2014-09-22 aearrivaltime=1727 diag_scheme=1 diag_01=NA
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=1 invest_02=NA invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=222 treat_02=NA treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=7 _ERROR_=1 _N_=404
NOTE: Invalid data for aeattenddisp in line 406 135-136.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
406 1cee00bf88465ac522fed6ac95772722,1794f12639335a737beafb1b90a757a5,NHSNO,RFR,VALID,1,A,0
88 13,13,e1b8ffb57ff16e542780088a4d840925,RFRPA,2,NA,01,01,2014-08-09,1502,01,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,27,511,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,NA,1 272
aekey=1cee00bf88465ac522fed6ac95772722 patient_id=1794f12639335a737beafb1b90a757a5
link_method=NHSNO procodet=RFR postcode_flag=VALID sex=1 ethnos=A activage=013 arrivalage=13
pgpprac=e1b8ffb57ff16e542780088a4d840925 sitetret=RFRPA aearrivalmode=2 aeattenddisp=.
aerefsource=1 aedepttype=1 aearrivaldate=2014-08-09 aearrivaltime=1502 diag_scheme=1 diag_01=NA
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=24 invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=27 treat_02=511 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=1 _ERROR_=1 _N_=405
NOTE: 1536914 records were read from the infile 'I:\cYorkshire Project\Care pathway for
asthma\Sarwat\Data\Datasets\2019-04-01-ace\ACE_AE_20190401.csv'.
The minimum record length was 237.
The maximum record length was 322.
NOTE: The data set WORK.AANDE has 1536914 observations and 55 variables.
NOTE: DATA statement used (Total process time):
real time 9.79 seconds
cpu time 5.89 seconds

Errors detected in submitted DATA step. Examine log.
1536914 rows created in WORK.AANDE from I:\cYorkshire Project\Care pathway for
asthma\Sarwat\Data\Datasets\2019-04-01-ace\ACE_AE_20190401.csv.

ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 10.83 seconds
cpu time 6.01 seconds

}

Thanks

Re: Invalid data error

Kurt_Bremser — Tue, 30 Jul 2019 11:02:38 GMT

That's why one DOES NOT use proc import.

Proc import is trying to read 'NA' as a numerical value.

Increase the guessingrows= value, so that the 'NA' are caught; but that is only half the issue. Most probably you want to have numeric values in there, so you first have to read these as character into a temporary variable, and then convert the contents:

if _aerefsource ne 'NA' then aerefsource = input(_aerefsource,best.);
drop _aerefsource;

(if _aerefsource is 'NA' then aerefsource will stay missing, as intended)

Re: Invalid data error

Ksharp — Tue, 30 Jul 2019 11:43:51 GMT

As Kurt pointed out .

Add

guessingrows=max ;

into your PROC IMPORT .

Re: Invalid data error

sks521 — Mon, 12 Aug 2019 10:31:46 GMT

Thanks,

So I have now tried writing up 'Infile' statement to get my data into SAS as follows;

{

DATA AandE;
INFILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv' firstobs=2 DSD MISSOVER;
input aekey $ patient_id $ link_method $ procodet $ postcode_flag $ sex $ ethnos $ activage $ arrivalage
pgpprac $ sitetret $ aearrivalmode $ aeattenddisp $ aerefsource $ aedepttype $ aearrivaldate DDMMYY10. aearrivaltime diag_scheme
diag_01 $ diag_02 $ diag_03 $ diag_04 $ diag_05 $ diag_06 $ diag_07 $ diag_08 $ diag_09 $ diag_10 $ diag_11 $
diag_12 $ invest_01 $ invest_02 $ invest_03 $ invest_04 $ invest_05 $ invest_06 $ invest_07 $ invest_08 $ invest_09 $
invest_10 $ invest_11 $ invest_12 $ treat_01 $ treat_02 $ treat_03 $ treat_04 $ treat_05 $ treat_06 $
treat_07 $ treat_08 $ treat_09 $ treat_10 $ treat_11 $ treat_12 $ imd15decile_overall;
if arrivalage in (0.001, 0.011, 0.049, 0.164, 0.375, 0.625, 0.874, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);
if aearrivaldate not in ('');

run;

}

SAS log is;

19 DATA AandE;
20 INFILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv' firstobs=2 DSD MISSOVER;
21 input aekey $ patient_id $ link_method $ procodet $ postcode_flag $
21 ! sex $ ethnos $ activage $ arrivalage
22 pgpprac $ sitetret $ aearrivalmode $ aeattenddisp $ aerefsource $ aedepttype $
22 ! aearrivaldate DDMMYY10. aearrivaltime diag_scheme
23 diag_01 $ diag_02 $ diag_03 $ diag_04 $ diag_05 $ diag_06 $ diag_07 $
23 ! diag_08 $ diag_09 $ diag_10 $ diag_11 $
24 diag_12 $ invest_01 $ invest_02 $ invest_03 $ invest_04 $ invest_05 $ invest_06 $
24 ! invest_07 $ invest_08 $ invest_09 $
25 invest_10 $ invest_11 $ invest_12 $ treat_01 $ treat_02 $ treat_03 $
25 ! treat_04 $ treat_05 $ treat_06 $
26 treat_07 $ treat_08 $ treat_09 $ treat_10 $ treat_11 $ treat_12 $
26 ! imd15decile_overall;
27 if arrivalage in (0.001, 0.011, 0.049, 0.164, 0.375, 0.625, 0.874, 1, 2, 3, 4, 5, 6, 7,
27 ! 8, 9, 10, 11, 12, 13, 14, 15, 16);
28 * if aearrivaldate not in ('1801-01-01');
29 /* output will only include children 0-16 years */
30 *if procodet not in ("RCF" , "RFF" , "RAE", "RWY", "RP5", "RCD", "RWA", "RR8", "RXF" ,
30 ! "RJL" , "RCU" , "RHQ" , "RFR" , "RCB") then delete;
31 /*this will get data on Yorkshire and humber NHS Trusts only */
32 *List of NHS Trusts in Yorkshire & Hmber include Airdale NHS Foundation Trust coded as
32 ! RCF, Barnsley as RFF, Bradford as RAE, Calderdale as RWY, Doncaster and Bassetlaw as RP5,
33 Harrogate and district as RCD, Hull university teaching hospital in the provider code
33 ! list but in the data dictionary it's Hull and East Yorkshire hospitals coded as RWA,
34 Leeds teaching hospital as RR8, Mid Yorkshire hospitals as RXF, Northern Lincolnshire
34 ! and coole as RJL, Sheffield Children's NHS foundation trust RCU,
35 Sheffield teaching hospital as RHQ, rotheram as RFR and York teaching hospital as RCB;
36 RUN;

NOTE: The infile 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv' is:
Filename=I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv,
RECFM=V,LRECL=32767,
File Size (bytes)=420517409,
Last Modified=01 April 2019 18:58:31,
Create Time=29 July 2019 16:34:07

NOTE: Invalid data for aearrivaldate in line 2 141-150.
NOTE: Invalid data for imd15decile_overall in line 2 265-266.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
2 e81e201e78d19d8c3f5122ce7d96c25a,97e023526b0dc2b5e9e08eed37775ab8,NHSNO,RXF,VALID,1,A,0
88 02,73,da4863e90b0b5a360e63d7df2e28815c,NA,2,03,01,01,2013-06-09,1014,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,34,41,NA,NA,NA,NA,NA,NA,NA,NA,
262 NA,NA,3 268
aekey=e81e201e patient_id=97e02352 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1
ethnos=A activage=002 arrivalage=73 pgpprac=da4863e9 sitetret=NA aearrivalmode=2 aeattenddisp=03
aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1014 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=24 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=34 treat_03=41 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=1
NOTE: Invalid data for aearrivaldate in line 3 141-150.
NOTE: Invalid data for imd15decile_overall in line 3 267-268.
3 5221b6afed59cee127663fe106688fd0,b76acf7d6e16213737184da41a1794a1,NHSNO,RXF,VALID,2,J,0
88 06,82,37d3d7784bee79f316d64b46e9c6dc78,NA,2,03,01,01,2013-06-28,1359,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,01,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,221,222,NA,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,3 270
aekey=5221b6af patient_id=b76acf7d link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=J activage=006 arrivalage=82 pgpprac=37d3d778 sitetret=NA aearrivalmode=2 aeattenddisp=03
aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1359 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=01 invest_03=24 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=221 treat_03=222 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=2
NOTE: Invalid data for aearrivaldate in line 4 143-152.
NOTE: Invalid data for imd15decile_overall in line 4 269-270.
4 c5f9d619392b7317bff4355d7e0c3829,8e19bf9b06f32ea3538b417a69254e3f,NHSNO,RCB,VALID,1,A,N
88 A,16,b6a4f27069e741c4c88b4c9975cd46bd,RCB55,2,03,01,01,2012-06-21,1602,01,NA,NA,NA,NA,N
175 A,NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,221,011,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,NA,3 272
aekey=c5f9d619 patient_id=8e19bf9b link_method=NHSNO procodet=RCB postcode_flag=VALID sex=1
ethnos=A activage=NA arrivalage=16 pgpprac=b6a4f270 sitetret=RCB55 aearrivalmode=2
aeattenddisp=03 aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1602
diag_01=01 diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=NA invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=221 treat_03=011 treat_04=NA treat_05=NA treat_06=NA
treat_07=NA treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=.
_ERROR_=1 _N_=3
NOTE: Invalid data for aearrivaldate in line 5 144-153.
NOTE: Invalid data for imd15decile_overall in line 5 273-274.
5 42bdbdc79100370c0f7b92e8219c950f,2a44ce4db8a93eb5a111d0900a818351,NHSNO,RWY,VALID,2,A,0
88 16,16,6868ed2cc119330c86104685a1100ecb,RWY01,2,03,01,01,2012-04-21,0231,01,05 15L,NA,NA
175 ,NA,NA,NA,NA,NA,NA,NA,NA,NA,01,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,511,NA,NA,NA,NA,NA,NA,N
262 A,NA,NA,NA,NA,9 276
aekey=42bdbdc7 patient_id=2a44ce4d link_method=NHSNO procodet=RWY postcode_flag=VALID sex=2
ethnos=A activage=016 arrivalage=16 pgpprac=6868ed2c sitetret=RWY01 aearrivalmode=2
aeattenddisp=03 aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=231
diag_01=01 diag_02=05 15L diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=01 invest_03=01 invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=511 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=4
NOTE: Invalid data for aearrivaldate in line 6 141-150.
NOTE: Invalid data for imd15decile_overall in line 6 266-267.
6 b43c9c6090a4a0eb69b5bd88f8b76e8f,1e46f877744f9cceec87405da4716053,NHSNO,RXF,VALID,2,A,0
88 15,21,b9e9dcb35b94dc784ba3a480d40ed041,NA,2,01,01,01,2013-06-17,1423,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,21,511,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,4 269
aekey=b43c9c60 patient_id=1e46f877 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=A activage=015 arrivalage=21 pgpprac=b9e9dcb3 sitetret=NA aearrivalmode=2 aeattenddisp=01
aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1423 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=21 treat_03=511 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=5
NOTE: Invalid data for aearrivaldate in line 7 144-153.
NOTE: Invalid data for imd15decile_overall in line 7 273-274.
7 dd8c32086cfd590049fdeb9817083967,f21138c84b7ea596bb1aeb4a7cbb1e32,NHSNO,RWA,VALID,1,A,0
88 16,16,f6b0b1b3a917196df5ac8dedbf282b03,RWA00,2,02,01,01,2012-02-28,1123,01,03 25L,NA,NA
175 ,NA,NA,NA,NA,NA,NA,NA,NA,NA,03,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,291,43,21,12,NA,NA,NA,N
262 A,NA,NA,NA,NA,3 276
aekey=dd8c3208 patient_id=f21138c8 link_method=NHSNO procodet=RWA postcode_flag=VALID sex=1
ethnos=A activage=016 arrivalage=16 pgpprac=f6b0b1b3 sitetret=RWA00 aearrivalmode=2
aeattenddisp=02 aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1123
diag_01=01 diag_02=03 25L diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=03 invest_03=24 invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=291 treat_03=43 treat_04=21 treat_05=12 treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=6
NOTE: Invalid data for aearrivaldate in line 8 131-140.
NOTE: Invalid data for imd15decile_overall in line 8 258-259.
8 c810915d6201106c5e9caec870183a21,23f76559afb9135cb92fbcf9072d9957,FULL ID NHSNO LINK,RJ
88 L,VALID,1,A,016,16,V81999,RJL32,2,03,01,01,2012-01-21,1601,01,NA,NA,NA,NA,NA,NA,NA,NA,N
175 A,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,21,236,042,NA,NA,NA,NA,NA,NA,NA,NA,8 261
aekey=c810915d patient_id=23f76559 link_method=FULL ID procodet=RJL postcode_flag=VALID sex=1
ethnos=A activage=016 arrivalage=16 pgpprac=V81999 sitetret=RJL32 aearrivalmode=2 aeattenddisp=03
aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1601 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=222 treat_03=21 treat_04=236 treat_05=042 treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=7
NOTE: Invalid data for aearrivaldate in line 9 144-153.
NOTE: Invalid data for imd15decile_overall in line 9 268-269.
9 4c8808798cb8278f03540122edb7f1b1,1152df925fcc5d927f5e811485338fc0,NHSNO,RP5,VALID,2,A,0
88 16,16,8e29e8d20b7b2d694d4251505166202c,RP5DR,2,02,08,01,2012-04-11,2115,01,38,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,99,NA,NA,NA,NA,NA,NA,NA,NA,
262 NA,NA,NA,5 271
aekey=4c880879 patient_id=1152df92 link_method=NHSNO procodet=RP5 postcode_flag=VALID sex=2
ethnos=A activage=016 arrivalage=16 pgpprac=8e29e8d2 sitetret=RP5DR aearrivalmode=2
aeattenddisp=02 aerefsource=08 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=2115
diag_01=01 diag_02=38 diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=NA invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=99 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=8
NOTE: Invalid data for aearrivaldate in line 10 144-153.
NOTE: Invalid data for imd15decile_overall in line 10 269-270.
10 4ee99a96e5cdee139f6c7b4a3ec31734,1152df925fcc5d927f5e811485338fc0,NHSNO,RP5,VALID,2,A,0
88 16,16,8e29e8d20b7b2d694d4251505166202c,RP5DR,2,05,01,01,2012-08-16,2029,01,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,51,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,NA,5 272
aekey=4ee99a96 patient_id=1152df92 link_method=NHSNO procodet=RP5 postcode_flag=VALID sex=2
ethnos=A activage=016 arrivalage=16 pgpprac=8e29e8d2 sitetret=RP5DR aearrivalmode=2
aeattenddisp=05 aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=2029
diag_01=01 diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=01 invest_03=NA invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=222 treat_03=51 treat_04=NA treat_05=NA treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=9
NOTE: Invalid data for aearrivaldate in line 11 144-153.
NOTE: Invalid data for imd15decile_overall in line 11 269-270.
11 ab1c30f4ae71acebfc5dc9cff812ae53,3ab4b9ec7420ffa8988cfa496116359c,NHSNO,RCD,VALID,2,A,0
88 16,16,eade35a693a9b16e87df763977afb438,RCD00,9,03,08,01,2012-04-07,1933,01,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,NA,5 272
aekey=ab1c30f4 patient_id=3ab4b9ec link_method=NHSNO procodet=RCD postcode_flag=VALID sex=2
ethnos=A activage=016 arrivalage=16 pgpprac=eade35a6 sitetret=RCD00 aearrivalmode=9
aeattenddisp=03 aerefsource=08 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1933
diag_01=01 diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=01 invest_03=NA invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=222 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1
_N_=10
NOTE: Invalid data for aearrivaldate in line 12 141-150.
NOTE: Invalid data for imd15decile_overall in line 12 265-266.
12 002c5a7a483c7f9a281393e9d5886edd,36c1b661c6472d7c520013558fab9993,NHSNO,RXF,VALID,1,A,0
88 12,41,e7c400b0c9b7de3d6465d4525908c955,NA,2,12,01,04,2013-06-03,0952,01,38,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,27,NA,NA,NA,NA,NA,NA,NA,NA,NA,
262 NA,NA,2 268
aekey=002c5a7a patient_id=36c1b661 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1
ethnos=A activage=012 arrivalage=41 pgpprac=e7c400b0 sitetret=NA aearrivalmode=2 aeattenddisp=12
aerefsource=01 aedepttype=04 aearrivaldate=. aearrivaltime=. diag_scheme=952 diag_01=01
diag_02=38 diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=24 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=27 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=11
NOTE: Invalid data for aearrivaldate in line 13 141-150.
NOTE: Invalid data for imd15decile_overall in line 13 266-267.
13 dcc81124ce35647223c9d78c59bfa4c9,0bb5d959bd1e72c06a523e9179f2325a,NHSNO,RXF,VALID,2,A,0
88 07,20,51c5f674fc8d4674054fc56574548648,NA,2,02,00,01,2013-06-12,1126,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,10,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,21,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,2 269
aekey=dcc81124 patient_id=0bb5d959 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=A activage=007 arrivalage=20 pgpprac=51c5f674 sitetret=NA aearrivalmode=2 aeattenddisp=02
aerefsource=00 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1126 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=10 invest_03=24 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=21 treat_03=222 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=12
NOTE: Invalid data for aearrivaldate in line 14 141-150.
NOTE: Invalid data for imd15decile_overall in line 14 266-267.
14 7fe0f97fad130b4cb0f2582fad3a3fd2,64679614526ef9ac7bf460b5b09495ca,NHSNO,RXF,VALID,2,A,0
88 08,88,8d1cfce8fc671545bfb10503dbfcabaa,NA,1,01,03,01,2013-06-27,1755,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,21,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,10 270
aekey=7fe0f97f patient_id=64679614 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=A activage=008 arrivalage=88 pgpprac=8d1cfce8 sitetret=NA aearrivalmode=1 aeattenddisp=01
aerefsource=03 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1755 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=24 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=21 treat_03=222 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=13
NOTE: Invalid data for aearrivaldate in line 15 141-150.
NOTE: Invalid data for imd15decile_overall in line 15 266-267.
15 9c346f3bf96e86c5c11e4c97c8db930d,6cc798e3ad901302d5df814f3fe7334a,NHSNO,RXF,VALID,1,R,0
88 02,73,1e3bcd6cf64f82d0f570a8962437f633,NA,1,01,00,01,2013-06-14,1238,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,01,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,21,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,3 269
aekey=9c346f3b patient_id=6cc798e3 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=1
ethnos=R activage=002 arrivalage=73 pgpprac=1e3bcd6c sitetret=NA aearrivalmode=1 aeattenddisp=01
aerefsource=00 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1238 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=01 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=21 treat_03=222 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=14
NOTE: Invalid data for aearrivaldate in line 16 141-150.
NOTE: Invalid data for imd15decile_overall in line 16 266-267.
16 d1787cf846fb617e63153dbc7c901e87,12b080b9cc4ba31655ebfdd5c0ee3c27,NHSNO,RXF,VALID,2,J,0
88 11,67,2a39f9120297aeaaca766b1483bcbba8,NA,2,04,00,01,2013-06-04,1449,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,21,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,8 269
aekey=d1787cf8 patient_id=12b080b9 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=J activage=011 arrivalage=67 pgpprac=2a39f912 sitetret=NA aearrivalmode=2 aeattenddisp=04
aerefsource=00 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1449 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=NA invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=21 treat_03=222 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=15
NOTE: Invalid data for aearrivaldate in line 17 141-150.
NOTE: Invalid data for imd15decile_overall in line 17 266-267.
17 4f0e6017b9c6da5650d3972a61681599,12b080b9cc4ba31655ebfdd5c0ee3c27,NHSNO,RXF,VALID,2,L,0
88 13,67,2a39f9120297aeaaca766b1483bcbba8,NA,2,03,07,01,2013-06-07,1133,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,8 269
aekey=4f0e6017 patient_id=12b080b9 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=L activage=013 arrivalage=67 pgpprac=2a39f912 sitetret=NA aearrivalmode=2 aeattenddisp=03
aerefsource=07 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1133 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=24 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=222 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=16
NOTE: Invalid data for aearrivaldate in line 18 144-153.
NOTE: Invalid data for imd15decile_overall in line 18 268-269.
18 de5e1b9898373a106a4a16ad01cb25c3,50d356d07b3e92e1258e0b35e0cfcc09,NHSNO,RP5,VALID,2,A,0
88 16,16,50a35e60f27ff4668292dccdfa3e8828,RP5DR,1,02,01,01,2012-07-14,1352,01,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,01,02,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,25,12,NA,NA,NA,NA,NA,NA,NA,
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
262 NA,NA,NA,2 271
aekey=de5e1b98 patient_id=50d356d0 link_method=NHSNO procodet=RP5 postcode_flag=VALID sex=2
ethnos=A activage=016 arrivalage=16 pgpprac=50a35e60 sitetret=RP5DR aearrivalmode=1
aeattenddisp=02 aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1352
diag_01=01 diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=01 invest_03=02 invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=25 treat_03=12 treat_04=NA treat_05=NA treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1
_N_=17
NOTE: Invalid data for aearrivaldate in line 19 144-153.
NOTE: Invalid data for imd15decile_overall in line 19 268-269.
19 3f4118a4e4c224a7478e4b33bb3e6105,50d356d07b3e92e1258e0b35e0cfcc09,NHSNO,RP5,VALID,2,A,0
88 16,16,50a35e60f27ff4668292dccdfa3e8828,RP5DR,1,02,08,01,2012-08-15,1110,01,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,NA,02,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,99,NA,NA,NA,NA,NA,NA,NA,NA,
262 NA,NA,NA,2 271
aekey=3f4118a4 patient_id=50d356d0 link_method=NHSNO procodet=RP5 postcode_flag=VALID sex=2
ethnos=A activage=016 arrivalage=16 pgpprac=50a35e60 sitetret=RP5DR aearrivalmode=1
aeattenddisp=02 aerefsource=08 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1110
diag_01=01 diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA
diag_09=NA diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=02 invest_03=NA invest_04=NA
invest_05=NA invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA
invest_12=NA treat_01=NA treat_02=99 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA
treat_08=NA treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1
_N_=18
NOTE: Invalid data for aearrivaldate in line 20 141-150.
NOTE: Invalid data for imd15decile_overall in line 20 266-267.
20 cdea90793c93c0f14a37e72146351a22,52712651674edb68a015eedfeaa65f48,NHSNO,RXF,VALID,2,A,0
88 04,29,332b1c9a7755133784452aa2e73495cd,NA,2,03,01,01,2013-06-30,0710,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,24,24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,21,222,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,9 269
aekey=cdea9079 patient_id=52712651 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=A activage=004 arrivalage=29 pgpprac=332b1c9a sitetret=NA aearrivalmode=2 aeattenddisp=03
aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=710 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=24 invest_03=24 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=21 treat_03=222 treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=19
NOTE: Invalid data for aearrivaldate in line 21 141-150.
NOTE: Invalid data for imd15decile_overall in line 21 266-267.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
21 96a372e7c7f98c481da3fb70a6f22ce1,78240b57dcf604ff84263a80e7e15a2b,NHSNO,RXF,VALID,2,A,0
88 09,27,5a1d8e249b4fbd67ad17e4654a4ec7ca,NA,2,06,01,01,2013-06-24,1306,01,NA,NA,NA,NA,NA,
175 NA,NA,NA,NA,NA,NA,NA,03,03,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,222,NA,NA,NA,NA,NA,NA,NA,NA,NA
262 ,NA,NA,8 269
aekey=96a372e7 patient_id=78240b57 link_method=NHSNO procodet=RXF postcode_flag=VALID sex=2
ethnos=A activage=009 arrivalage=27 pgpprac=5a1d8e24 sitetret=NA aearrivalmode=2 aeattenddisp=06
aerefsource=01 aedepttype=01 aearrivaldate=. aearrivaltime=. diag_scheme=1306 diag_01=01
diag_02=NA diag_03=NA diag_04=NA diag_05=NA diag_06=NA diag_07=NA diag_08=NA diag_09=NA
diag_10=NA diag_11=NA diag_12=NA invest_01=NA invest_02=03 invest_03=03 invest_04=NA invest_05=NA
invest_06=NA invest_07=NA invest_08=NA invest_09=NA invest_10=NA invest_11=NA invest_12=NA
treat_01=NA treat_02=222 treat_03=NA treat_04=NA treat_05=NA treat_06=NA treat_07=NA treat_08=NA
treat_09=NA treat_10=NA treat_11=NA treat_12=NA imd15decile_overall=. _ERROR_=1 _N_=20
NOTE: 1536914 records were read from the infile
'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv'.
The minimum record length was 237.
The maximum record length was 322.
NOTE: The data set WORK.AANDE has 1533841 observations and 55 variables.
NOTE: DATA statement used (Total process time):
real time 11.76 seconds
cpu time 7.12 seconds

}

And I am getting 'aearrivaldate' as missing for all observations.

Any help will be much appreciated.

Thanks

Re: Invalid data error

Tom — Mon, 12 Aug 2019 12:50:51 GMT

When reading from a delimited file you should use LIST MODE input style. Not FORMATTED mode. This means if you include an informat specifications in the INPUT statement they must be preceded by the colon modifier.

        aearrivaldate :DDMMYY10.

PS. Putting { } around your code doesn't really do anything in the forum editor. Use the Insert Code button in the editor to get a pop-up window to paste the block of code.

Note: It is normally a lot easier to just define the variables using a LENGTH statement instead of forcing SAS to guess how to define them by how you first use them in other statements like, INPUT, FORMAT, INFORMAT or assignments. If you define them in the order they appear in the input then your input statement can just use a positional variable list.

input aekey-- imd15decile_overall;

Re: Invalid data error

Tom — Mon, 12 Aug 2019 12:57:50 GMT

Can you ask the creator of the file to NOT put those NA strings into the numeric columns?

Or perhaps you can just pre-process the file and remove them and then import from the cleaned up file?

data _null_;
  INFILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv' dsd truncover length=len column=cc ;
  FILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv' dsd;
  length string $500 ;
  do until(cc > len);
    input string @ ; 
    if string = 'NA' then string=' ';
    put string @;
  end;
  put;
run;

Re: Invalid data error

sks521 — Mon, 12 Aug 2019 14:07:34 GMT

Wish I could! Data is extracted from the National Health Services routine data and observations with NA in these fields are needed in further analysis.

Re: Invalid data error

Tom — Mon, 12 Aug 2019 14:12:11 GMT

You can also generate a user defined INFORMAT that will translate the NA into missing or special missing.

For example you could read them as .N using this informat defintion.

proc format ;
invalue na 'NA'=.N other=_same_;
run;

data test;
  informat x na. ;
  input x ;
cards;
1
2.3
NA
5.6
;
proc print; run;

Re: Invalid data error

sks521 — Mon, 12 Aug 2019 14:57:18 GMT

Thanks but how do I incorporate the rest of input fields in this syntax.

DATA AandE;
INFILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv' firstobs=2 DSD truncover length=len column=cc ;
FILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv' dsd;
  length string $500 ;
  do until(cc > len);
    input string @ aekey  $    patient_id  $   link_method  $     procodet  $     postcode_flag  $    sex $    ethnos  $       activage  $       arrivalage           
  pgpprac  $   sitetret  $  aearrivalmode $   aeattenddisp $  aerefsource $  aedepttype $    aearrivaldate :DDMMYY10.  aearrivaltime    diag_scheme
   diag_01  $   diag_02  $  diag_03  $  diag_04  $  diag_05  $  diag_06  $  diag_07  $   diag_08  $  diag_09  $ diag_10  $  diag_11  $            
  diag_12  $  invest_01 $  invest_02  $ invest_03  $ invest_04  $  invest_05  $ invest_06  $  invest_07  $ invest_08  $  invest_09  $
    invest_10  $  invest_11  $  invest_12  $  treat_01  $  treat_02  $  treat_03  $  treat_04  $  treat_05  $   treat_06  $
      treat_07  $   treat_08  $  treat_09  $  treat_10  $  treat_11  $  treat_12  $  imd15decile_overall $;
	 if arrivalage in (0.001, 0.011, 0.049, 0.164, 0.375, 0.625, 0.874, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); 
	if aearrivaldate not in (''); 
    if string = 'NA' then string=' ';
    put string @;
  end;
  put;
run;

Re: Invalid data error

Tom — Mon, 12 Aug 2019 14:59:43 GMT

You don't. Use the step I posted to fix the file. Then write a new step that reads the fixed file.

Re: Invalid data error

sks521 — Tue, 13 Aug 2019 08:52:13 GMT

Thanks. I have written up this syntax but still getting errors

data _null_;
  INFILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv' dsd truncover length=len column=cc ;
  FILE 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv' dsd;
  length string $500 ;
  do until(cc > len);
    input string @ ; 
    if string = 'NA' then string=' ';
    put string @;
  end;
  put;
run;
data Sheffield;
infile 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv';
 input   aekey  $    patient_id  $   link_method  $     procodet  $     postcode_flag  $    sex $    ethnos  $       activage  $       arrivalage           
  pgpprac  $   sitetret  $  aearrivalmode $   aeattenddisp $  aerefsource $  aedepttype $    aearrivaldate :DDMMYY10.  aearrivaltime    diag_scheme
   diag_01  $   diag_02  $  diag_03  $  diag_04  $  diag_05  $  diag_06  $  diag_07  $   diag_08  $  diag_09  $ diag_10  $  diag_11  $            
  diag_12  $  invest_01 $  invest_02  $ invest_03  $ invest_04  $  invest_05  $ invest_06  $  invest_07  $ invest_08  $  invest_09  $
    invest_10  $  invest_11  $  invest_12  $  treat_01  $  treat_02  $  treat_03  $  treat_04  $  treat_05  $   treat_06  $
      treat_07  $   treat_08  $  treat_09  $  treat_10  $  treat_11  $  treat_12  $  imd15decile_overall $;
	if arrivalage in (0.001, 0.011, 0.049, 0.164, 0.375, 0.625, 0.874, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); 
	if aearrivaldate not in ('');
run;

Re: Invalid data error

sks521 — Tue, 13 Aug 2019 08:53:41 GMT

Please see the log below (just a part of the log);

NOTE: The infile 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv' is:
      Filename=I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv,
      RECFM=V,LRECL=32767,
      File Size (bytes)=420517409,
      Last Modified=01 April 2019 18:58:31,
      Create Time=29 July 2019 16:34:07

NOTE: The file 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv' is:
      Filename=I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=13 August 2019 09:22:34,
      Create Time=13 August 2019 09:22:08

NOTE: 1536915 records were read from the infile
      'I:\CHCSheffield\20190401ACE\ACE_AE_20190401.csv'.
      The minimum record length was 237.
      The maximum record length was 533.
NOTE: 1536915 records were written to the file
      'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv'.
      The minimum record length was 160.
      The maximum record length was 533.
NOTE: DATA statement used (Total process time):
      real time           59.46 seconds
      cpu time            49.85 seconds


23   data Sheffield;
24   infile 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv';
25    input   aekey  $    patient_id  $   link_method  $     procodet  $     postcode_flag  $
25 ! sex $    ethnos  $       activage  $       arrivalage
26     pgpprac  $   sitetret  $  aearrivalmode $   aeattenddisp $  aerefsource $  aedepttype $
26 !  aearrivaldate :DDMMYY10.  aearrivaltime    diag_scheme
27      diag_01  $   diag_02  $  diag_03  $  diag_04  $  diag_05  $  diag_06  $  diag_07  $
27 ! diag_08  $  diag_09  $ diag_10  $  diag_11  $
28     diag_12  $  invest_01 $  invest_02  $ invest_03  $ invest_04  $  invest_05  $ invest_06  $
28 !   invest_07  $ invest_08  $  invest_09  $
29       invest_10  $  invest_11  $  invest_12  $  treat_01  $  treat_02  $  treat_03  $
29 ! treat_04  $  treat_05  $   treat_06  $
30         treat_07  $   treat_08  $  treat_09  $  treat_10  $  treat_11  $  treat_12  $
30 ! imd15decile_overall $;
31       if arrivalage in (0.001, 0.011, 0.049, 0.164, 0.375, 0.625, 0.874, 1, 2, 3, 4, 5, 6, 7,
31 ! 8, 9, 10, 11, 12, 13, 14, 15, 16);
32       if aearrivaldate not in ('');
33         /* output will only include children 0-16 years */
34         *if procodet not in ("RCF" , "RFF" , "RAE", "RWY", "RP5", "RCD", "RWA", "RR8", "RXF" ,
34 !  "RJL" , "RCU" , "RHQ" , "RFR" , "RCB") then delete;
35         /*this will get data on Yorkshire and humber NHS Trusts only */
36      *List of NHS Trusts in Yorkshire & Hmber include Airdale NHS Foundation Trust coded as
36 ! RCF, Barnsley as RFF, Bradford as RAE, Calderdale as RWY, Doncaster and Bassetlaw as RP5,
37         Harrogate and district as RCD, Hull university teaching hospital in the provider code
37 ! list but in the data dictionary it's Hull and East Yorkshire hospitals coded as RWA,
38         Leeds teaching hospital as RR8, Mid Yorkshire hospitals as RXF, Northern Lincolnshire
38 ! and coole as RJL, Sheffield Children's NHS foundation trust RCU,
39         Sheffield teaching hospital as RHQ, rotheram as RFR and York teaching hospital as RCB;
40   RUN;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      32:8
NOTE: The infile 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv' is:
      Filename=I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv,
      RECFM=V,LRECL=32767,
      File Size (bytes)=320984319,
      Last Modified=13 August 2019 09:23:33,
      Create Time=13 August 2019 09:22:08

NOTE: Invalid data for arrivalage in line 7 166-218.
NOTE: Invalid data for aearrivaldate in line 11 1-204.
NOTE: Invalid data for aearrivaltime in line 12 1-202.
NOTE: Invalid data for diag_scheme in line 13 1-203.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
48        4eb5fc3e9fb012014520eed3678c1dd9,f6e6042beab2e859cbd78ae3464725a9,NHSNO,RJL,VALID,2,A,0
      88  16,16,5b999ca47f019b09a77ff0a44ff823b9,RJL32,2,03,01,01,2012-03-02,1713,01,,,,,,,,,,,,,
     175  24,,,,,,,,,,,,236,222,,,,,,,,,,,7 207
NOTE: Invalid data errors for file ''I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv''
      occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
aekey=aekey,pa patient_id=e81e201e link_method=5221b6af procodet=c5f9d619 postcode_flag=42bdbdc7
sex=15L,,,,, ethnos=b43c9c60 activage=dd8c3208 arrivalage=. pgpprac=c810915d sitetret=ID
aearrivalmode=NHSNO aeattenddisp=LINK,RJL aerefsource=4c880879 aedepttype=4ee99a96
aearrivaldate=. aearrivaltime=. diag_scheme=. diag_01=7fe0f97f diag_02=9c346f3b diag_03=d1787cf8
diag_04=4f0e6017 diag_05=de5e1b98 diag_06=3f4118a4 diag_07=cdea9079 diag_08=96a372e7
diag_09=3356a335 diag_10=a05d152e diag_11=609a673e diag_12=437074e3 invest_01=43719689
invest_02=5e70c062 invest_03=bcf368db invest_04=33R,,,,, invest_05=427ecb5f invest_06=427134a9
invest_07=e265db17 invest_08=261c8bc3 invest_09=c9d39729 invest_10=42b1512b invest_11=46061b79
invest_12=67fe548e treat_01=8de5ae17 treat_02=f537f23f treat_03=b0cd1fe5 treat_04=ea54a09d
treat_05=f9e765d5 treat_06=9cdb03d5 treat_07=f59af81d treat_08=17R,,,,, treat_09=c142e904
treat_10=65113e02 treat_11=ff2a08c2 treat_12=ebaf1d5e imd15decile_overall=4eb5fc3e _ERROR_=1

Re: Invalid data error

Tom — Tue, 13 Aug 2019 12:50:43 GMT

Why aren't you using the DSD option when reading the data?

Re: Invalid data error

sks521 — Tue, 13 Aug 2019 14:19:14 GMT

Used it but it's still not solving the problem because when I print eaarrivaldate variable, it's giving me 0 observations.

data Sheffield;
infile 'I:\CHCSheffield\20190401ACE\ACE_AE_20190401_fixed.csv' dsd;
 input   aekey  $    patient_id  $   link_method  $     procodet  $     postcode_flag  $    sex $    ethnos  $       activage  $       arrivalage $          
  pgpprac  $   sitetret  $  aearrivalmode $   aeattenddisp $  aerefsource $  aedepttype $    aearrivaldate DDMMYY10.  aearrivaltime    diag_scheme $
   diag_01  $   diag_02  $  diag_03  $  diag_04  $  diag_05  $  diag_06  $  diag_07  $   diag_08  $  diag_09  $ diag_10  $  diag_11  $            
  diag_12  $  invest_01 $  invest_02  $ invest_03  $ invest_04  $  invest_05  $ invest_06  $  invest_07  $ invest_08  $  invest_09  $
    invest_10  $  invest_11  $  invest_12  $  treat_01  $  treat_02  $  treat_03  $  treat_04  $  treat_05  $   treat_06  $
      treat_07  $   treat_08  $  treat_09  $  treat_10  $  treat_11  $  treat_12  $  imd15decile_overall $;
	if arrivalage in (0.001, 0.011, 0.049, 0.164, 0.375, 0.625, 0.874, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); 
run;

Re: Invalid data error

Tom — Tue, 13 Aug 2019 14:32:16 GMT

Now that you have removed the NA text from the numeric fields why are you still reading them as character variables? Does you file have a header row? You don't have FIRSTOBS=2 to skip over it. You also didn't include the TRUNCOVER option (a large improvement over the older MISSOVER option) to prevent SAS from going to the next line if the last variable's value is missing.

Did you try asking PROC IMPORT to try to guess how to define the variables now that the NA text is gone?

When writing my own data step to read from a delimited file I find it much easier to first DEFINE all of the variables using a LENGTH statement. If you define them in the order they appear in the input data then the INPUT statement can just use a simple position based variable list. Like:

INPUT aekey -- imd15decile_overall ;

If any of the variables NEED them you can use INFORMAT and/or FORMAT statement to then attach informats or formats to the variables. For most data SAS does not need to have either and informat or a format attached. The main exception are DATE, TIME and DATETIME values.

Re: Invalid data error

sks521 — Wed, 14 Aug 2019 12:06:33 GMT

I have tried both proc import to guess how to define the variables which gives me this log; so I impute this information in infile statement which I will send in my next reply

 proc import datafile = 'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv'
2     out  = Sheffield
3     dbms  =  csv;
4    run;

5     /**********************************************************************
6     *   PRODUCT:   SAS
7     *   VERSION:   9.4
8     *   CREATOR:   External File Interface
9     *   DATE:      14AUG19
10    *   DESC:      Generated SAS Datastep Code
11    *   TEMPLATE SOURCE:  (None Specified.)
12    ***********************************************************************/
13       data WORK.SHEFFIELD    ;
14       %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
15       infile 'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv' delimiter = ','
15 ! MISSOVER DSD lrecl=32767 firstobs=2 ;
16          informat aekey $32. ;
17          informat procodet $3. ;
18          informat sex best32. ;
19          informat ethnos $1. ;
20          informat activage best32. ;
21          informat arrivalage best32. ;
22          informat pgpprac $32. ;
23          informat sitetret $5. ;
24          informat aearrivalmode best32. ;
25          informat aeattenddisp best32. ;
26          informat aerefsource best32. ;
27          informat aedepttype best32. ;
28          informat aearrivaldate ddmmyy10. ;
29          informat aearrivaltime best32. ;
30          informat diag_scheme best32. ;
31          informat diag_01 $6. ;
32          informat diag_02 $1. ;
33          informat diag_03 $1. ;
34          informat diag_04 $1. ;
35          informat diag_05 $1. ;
36          informat diag_06 $1. ;
37          informat invest_01 best32. ;
38          informat invest_02 best32. ;
39          informat invest_03 $1. ;
40          informat invest_04 $1. ;
41          informat invest_05 $1. ;
42          informat invest_06 $1. ;
43          informat invest_07 $1. ;
44          informat invest_08 $1. ;
45          informat invest_09 $1. ;
46          informat invest_10 $1. ;
47          informat invest_11 $1. ;
48          informat invest_12 $1. ;
49          informat treat_01 best32. ;
50          informat treat_02 best32. ;
51          informat treat_03 best32. ;
52          informat treat_04 best32. ;
53          informat treat_05 $1. ;
54          informat treat_06 $1. ;
55          informat treat_07 $1. ;
56          informat treat_08 $1. ;
57          informat treat_09 $1. ;
58          informat imd15decile_overall best32. ;
59          format aekey $32. ;
60          format procodet $3. ;
61          format sex best12. ;
62          format ethnos $1. ;
63          format activage best12. ;
64          format arrivalage best12. ;
65          format pgpprac $32. ;
66          format sitetret $5. ;
67          format aearrivalmode best12. ;
68          format aeattenddisp best12. ;
69          format aerefsource best12. ;
70          format aedepttype best12. ;
71          format aearrivaldate ddmmyy10. ;
72          format aearrivaltime best12. ;
73          format diag_scheme best12. ;
74          format diag_01 $6. ;
75          format diag_02 $1. ;
76          format diag_03 $1. ;
77          format diag_04 $1. ;
78          format diag_05 $1. ;
79          format diag_06 $1. ;
80          format invest_01 best12. ;
81          format invest_02 best12. ;
82          format invest_03 $1. ;
83          format invest_04 $1. ;
84          format invest_05 $1. ;
85          format invest_06 $1. ;
86          format invest_07 $1. ;
87          format invest_08 $1. ;
88          format invest_09 $1. ;
89          format invest_10 $1. ;
90          format invest_11 $1. ;
91          format invest_12 $1. ;
92          format treat_01 best12. ;
93          format treat_02 best12. ;
94          format treat_03 best12. ;
95          format treat_04 best12. ;
96          format treat_05 $1. ;
97          format treat_06 $1. ;
98          format treat_07 $1. ;
99          format treat_08 $1. ;
100         format treat_09 $1. ;
101         format imd15decile_overall best12. ;
102      input
103                  aekey  $
104                  procodet  $
105                  sex
106                  ethnos  $
107                  activage
108                  arrivalage
109                  pgpprac  $
110                  sitetret  $
111                  aearrivalmode
112                  aeattenddisp
113                  aerefsource
114                  aedepttype
115                  aearrivaldate
116                  aearrivaltime
117                  diag_scheme
118                  diag_01  $
119                  diag_02  $
120                  diag_03  $
121                  diag_04  $
122                  diag_05  $
123                  diag_06  $
124                  invest_01
125                  invest_02
126                  invest_03  $
127                  invest_04  $
128                  invest_05  $
129                  invest_06  $
130                  invest_07  $
131                  invest_08  $
132                  invest_09  $
133                  invest_10  $
134                  invest_11  $
135                  invest_12  $
136                  treat_01
137                  treat_02
138                  treat_03
139                  treat_04
140                  treat_05  $
141                  treat_06  $
142                  treat_07  $
143                  treat_08  $
144                  treat_09  $
145                  imd15decile_overall
146      ;
147      if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
148      run;

NOTE: The infile 'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv' is:
      Filename=I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv,
      RECFM=V,LRECL=32767,
      File Size (bytes)=155940219,
      Last Modified=14 August 2019 09:09:43,
      Create Time=13 August 2019 09:22:08

NOTE: 1048575 records were read from the infile
      'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv'.
      The minimum record length was 103.
      The maximum record length was 215.
NOTE: The data set WORK.SHEFFIELD has 1048575 observations and 43 variables.
NOTE: DATA statement used (Total process time):
      real time           3.65 seconds
      cpu time            2.67 seconds


1048575 rows created in WORK.SHEFFIELD from
I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv.



NOTE: WORK.SHEFFIELD data set was successfully created.
NOTE: The data set WORK.SHEFFIELD has 1048575 observations and 43 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           4.05 seconds
      cpu time            2.76 seconds

Re: Invalid data error

sks521 — Wed, 14 Aug 2019 12:10:51 GMT

This is infile statement;

data Sheffield;
infile 'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv' dsd truncover lrecl=32767;
INPUT  aekey  $ procodet  $ sex  ethnos  $  activage  arrivalage  pgpprac  $  sitetret  $   aearrivalmode  aeattenddisp    aerefsource    aedepttype   aearrivaldate ddmmyy10.
  aearrivaltime  diag_scheme  diag_01  $  diag_02  $  diag_03  $  diag_04  $  diag_05  $   diag_06  $   invest_01 $    invest_02 $      invest_03  $   invest_04  $
  invest_05  $   invest_06  $   invest_07  $   invest_08  $  invest_09  $    invest_10  $  invest_11  $   invest_12  $  treat_01     treat_02    treat_03 
 treat_04   treat_05  $  treat_06  $  treat_07  $   treat_08  $  treat_09  $  imd15decile_overall;

RUN;

Re: Invalid data error

sks521 — Wed, 14 Aug 2019 12:12:34 GMT

When I run it, it still gives me multiple errors see below;

 data Sheffield;
2    infile 'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv' dsd truncover
2  ! lrecl=32767;
3    INPUT  aekey  $ procodet  $ sex  ethnos  $  activage  arrivalage  pgpprac  $  sitetret  $
3  ! aearrivalmode  aeattenddisp    aerefsource    aedepttype   aearrivaldate ddmmyy10.
4      aearrivaltime  diag_scheme  diag_01  $  diag_02  $  diag_03  $  diag_04  $  diag_05  $
4  ! diag_06  $   invest_01 $    invest_02 $      invest_03  $   invest_04  $
5      invest_05  $   invest_06  $   invest_07  $   invest_08  $  invest_09  $    invest_10  $
5  ! invest_11  $   invest_12  $  treat_01     treat_02    treat_03
6     treat_04   treat_05  $  treat_06  $  treat_07  $   treat_08  $  treat_09  $
6  ! imd15decile_overall;
7
8    RUN;

NOTE: The infile 'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv' is:
      Filename=I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv,
      RECFM=V,LRECL=32767,
      File Size (bytes)=155940219,
      Last Modified=14 August 2019 09:09:43,
      Create Time=13 August 2019 09:22:08

NOTE: Invalid data for sex in line 1 16-18.
NOTE: Invalid data for activage in line 1 27-34.
NOTE: Invalid data for arrivalage in line 1 36-45.
NOTE: Invalid data for aearrivalmode in line 1 64-76.
NOTE: Invalid data for aeattenddisp in line 1 78-89.
NOTE: Invalid data for aerefsource in line 1 91-101.
NOTE: Invalid data for aedepttype in line 1 103-112.
NOTE: Invalid data for aearrivaldate in line 1 114-123.
NOTE: Invalid data for aearrivaltime in line 1 124-126.
NOTE: Invalid data for diag_scheme in line 1 128-140.
NOTE: Invalid data for treat_01 in line 1 312-320.
NOTE: Invalid data for treat_02 in line 1 322-329.
NOTE: Invalid data for treat_03 in line 1 331-338.
NOTE: Invalid data for treat_04 in line 1 340-347.
NOTE: Invalid data for imd15decile_overall in line 1 394-401.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
1         aekey,procodet,sex,ethnos,activage,arrivalage,pgpprac,sitetret,aearrivalmode,aeattenddi
      88  sp,aerefsource,aedepttype,aearrivaldate,aearrivaltime,diag_scheme,diag_01,diag_02,diag_
     175  03,diag_04,diag_05,diag_06,invest_01,invest_02,invest_03,invest_04,invest_05,invest_06,
     262  invest_07,invest_08,invest_09,invest_10,invest_11,invest_12,treat_01,treat_02,treat_03,
     349  treat_04,treat_05,treat_06,treat_07,treat_08,treat_09,imd15decile_overall 421
aekey=aekey procodet=procodet sex=. ethnos=ethnos activage=. arrivalage=. pgpprac=pgpprac
sitetret=sitetret aearrivalmode=. aeattenddisp=. aerefsource=. aedepttype=. aearrivaldate=.
aearrivaltime=. diag_scheme=. diag_01=diag_sch diag_02=diag_01 diag_03=diag_02 diag_04=diag_03
diag_05=diag_04 diag_06=diag_05 invest_01=diag_06 invest_02=invest_0 invest_03=invest_0
invest_04=invest_0 invest_05=invest_0 invest_06=invest_0 invest_07=invest_0 invest_08=invest_0
invest_09=invest_0 invest_10=invest_0 invest_11=invest_1 invest_12=invest_1 treat_01=. treat_02=.
treat_03=. treat_04=. treat_05=treat_04 treat_06=treat_05 treat_07=treat_06 treat_08=treat_07
treat_09=treat_08 imd15decile_overall=. _ERROR_=1 _N_=1
NOTE: 1048576 records were read from the infile
      'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv'.
      The minimum record length was 103.
      The maximum record length was 421.
NOTE: The data set WORK.SHEFFIELD has 1048576 observations and 43 variables.
NOTE: DATA statement used (Total process time):
      real time           4.55 seconds
      cpu time            3.21 seconds

Re: Invalid data error

sks521 — Wed, 14 Aug 2019 12:15:46 GMT

So, no I am completely lost! not sure if I catch what you mean by LENGHT statement

Re: Invalid data error

Tom — Wed, 14 Aug 2019 12:39:31 GMT

If you read the error messages and the line of data that is displayed in the log you will see that you are trying to read the header line as data. You just need to add FIRSTOBS=2 to your INFILE statement to have SAS skip the header row.

When SAS is compiling the data step it builds up the definitions of the variables based on how you first reference the variables. So your program the first reference is in the INPUT statement. This will cause SAS to guess how to define the variables based on how you use them in the INPUT statement. In your case you are putting the $ modifier after some of the variable names to tell the INPUT statement to read the value as a character string. So with that limited knowledge SAS will guess that you want the variables defined as character with a length of 8.

The ugly code that PROC IMPORT generates has a series of INFORMAT (most of them unneeded) statements as the first place where SAS sees the variable. So in this case SAS will guess how you wanted to define the variable based on the type of the informat and in the case of character variables the width of the informat used.

If you want have control and actually TELL the data step compiler how to define the variable then use them first in either a LENGTH statement or an ATTRIB statement.