Re: Invalid data error - Page 2

sks521 · Posted 08-14-2019 08:06 AM

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

sks521 · Posted 08-14-2019 08:10 AM

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;

sks521 · Posted 08-14-2019 08:12 AM

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

sks521 · Posted 08-14-2019 08:15 AM

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

Tom · Posted 08-14-2019 08:39 AM

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.

sks521 · Posted 08-14-2019 08:48 AM

Thanks Tom, I have understood most parts of your answer but can you give an example of how to use LENGTH or ATTIB statement please?

sks521 · Posted 08-14-2019 08:52 AM

On another note: I have added 'FIRSTOBS=2' and now it's reading the data OK but when I run the next steps, I still get errors on some values. Here is the code and log posted in the next post;

Data control1;
set Sheffield;
if aekey NE ' ' then visit=1;
*2-16 year olds with wheese/asthma diagnosis, investigation and treatment codes in the rest of Yorkshire & Humber;
if arrivalage <2 OR diag_01 - diag_06 NOT in ('251', 'J441', 'J45', 'J459', 'J46', 'J46X', 'R062', '7001', '7002', '7003', '7004') 
or treat_01 -treat_09 NOT in ('25', '40') then delete;
if procodet = 'RAE' then group1=1;
else group1=0;
run;

sks521 · Posted 08-14-2019 08:53 AM

Now the log; I defined diag_01 to diag_06 as character variables in my infile statement.

9    Data control1;
10   set Sheffield;
11   if aekey NE ' ' then visit=1;
12   *with 2-16 year olds with wheese/asthma diagnosis, investigation and treatment codes in the
12 ! rest of Yorkshire & Humber;
13   if arrivalage <2 OR diag_01 - diag_06 NOT in ('251', 'J441', 'J45', 'J459', 'J46', 'J46X',
13 ! 'R062', '7001', '7002', '7003', '7004')
14   or treat_01 -treat_09 NOT in ('25', '40') then delete;
15   if procodet = 'RAE' then group1=1;
16   else group1=0;
17   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      13:21   13:29   13:31   14:13   14:14
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=e81e201e procodet=RXF sex=1 ethnos=A activage=2 arrivalage=73 pgpprac=da4863e9 sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19518 aearrivaltime=.
diag_scheme=1014 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=34 treat_03=41 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=1
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=5221b6af procodet=RXF sex=2 ethnos=J activage=6 arrivalage=82 pgpprac=37d3d778 sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19537 aearrivaltime=.
diag_scheme=1359 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=1 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=221 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=2
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=c5f9d619 procodet=RCB sex=1 ethnos=A activage=. arrivalage=16 pgpprac=b6a4f270
sitetret=RCB55 aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19165
aearrivaltime=. diag_scheme=1602 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=221 treat_03=11 treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=3
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=42bdbdc7 procodet=RWY sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=6868ed2c
sitetret=RWY01 aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19104
aearrivaltime=. diag_scheme=231 diag_01=1 diag_02=05 15L diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=1 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=511 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=4
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=b43c9c60 procodet=RXF sex=2 ethnos=A activage=15 arrivalage=21 pgpprac=b9e9dcb3 sitetret=
aearrivalmode=2 aeattenddisp=1 aerefsource=1 aedepttype=1 aearrivaldate=19526 aearrivaltime=.
diag_scheme=1423 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=511 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=5
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=dd8c3208 procodet=RWA sex=1 ethnos=A activage=16 arrivalage=16 pgpprac=f6b0b1b3
sitetret=RWA00 aearrivalmode=2 aeattenddisp=2 aerefsource=1 aedepttype=1 aearrivaldate=19051
aearrivaltime=. diag_scheme=1123 diag_01=1 diag_02=03 25L diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=3 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=291 treat_03=43 treat_04=21
treat_05=12 treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=6
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=c810915d procodet=RJL sex=1 ethnos=A activage=16 arrivalage=16 pgpprac=V81999
sitetret=RJL32 aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19013
aearrivaltime=. diag_scheme=1601 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=21 treat_04=236
treat_05=42 treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=7
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=4c880879 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=8e29e8d2
sitetret=RP5DR aearrivalmode=2 aeattenddisp=2 aerefsource=8 aedepttype=1 aearrivaldate=19094
aearrivaltime=. diag_scheme=2115 diag_01=1 diag_02=38 diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=99 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=8
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=4ee99a96 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=8e29e8d2
sitetret=RP5DR aearrivalmode=2 aeattenddisp=5 aerefsource=1 aedepttype=1 aearrivaldate=19221
aearrivaltime=. diag_scheme=2029 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=51 treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=9
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=ab1c30f4 procodet=RCD sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=eade35a6
sitetret=RCD00 aearrivalmode=9 aeattenddisp=3 aerefsource=8 aedepttype=1 aearrivaldate=19090
aearrivaltime=. diag_scheme=1933 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=10
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=002c5a7a procodet=RXF sex=1 ethnos=A activage=12 arrivalage=41 pgpprac=e7c400b0 sitetret=
aearrivalmode=2 aeattenddisp=12 aerefsource=1 aedepttype=4 aearrivaldate=19512 aearrivaltime=.
diag_scheme=952 diag_01=1 diag_02=38 diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=27 treat_03=. treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=11
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=dcc81124 procodet=RXF sex=2 ethnos=A activage=7 arrivalage=20 pgpprac=51c5f674 sitetret=
aearrivalmode=2 aeattenddisp=2 aerefsource=0 aedepttype=1 aearrivaldate=19521 aearrivaltime=.
diag_scheme=1126 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=10 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=12
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=7fe0f97f procodet=RXF sex=2 ethnos=A activage=8 arrivalage=88 pgpprac=8d1cfce8 sitetret=
aearrivalmode=1 aeattenddisp=1 aerefsource=3 aedepttype=1 aearrivaldate=19536 aearrivaltime=.
diag_scheme=1755 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=13
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=9c346f3b procodet=RXF sex=1 ethnos=R activage=2 arrivalage=73 pgpprac=1e3bcd6c sitetret=
aearrivalmode=1 aeattenddisp=1 aerefsource=0 aedepttype=1 aearrivaldate=19523 aearrivaltime=.
diag_scheme=1238 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=1 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=14
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=d1787cf8 procodet=RXF sex=2 ethnos=J activage=11 arrivalage=67 pgpprac=2a39f912 sitetret=
aearrivalmode=2 aeattenddisp=4 aerefsource=0 aedepttype=1 aearrivaldate=19513 aearrivaltime=.
diag_scheme=1449 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=15
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=4f0e6017 procodet=RXF sex=2 ethnos=L activage=13 arrivalage=67 pgpprac=2a39f912 sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=7 aedepttype=1 aearrivaldate=19516 aearrivaltime=.
diag_scheme=1133 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=. treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=16
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=de5e1b98 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=50a35e60
sitetret=RP5DR aearrivalmode=1 aeattenddisp=2 aerefsource=1 aedepttype=1 aearrivaldate=19188
aearrivaltime=. diag_scheme=1352 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=2 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=25 treat_03=12 treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=17
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=3f4118a4 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=50a35e60
sitetret=RP5DR aearrivalmode=1 aeattenddisp=2 aerefsource=8 aedepttype=1 aearrivaldate=19220
aearrivaltime=. diag_scheme=1110 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=2 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=99 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=18
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=cdea9079 procodet=RXF sex=2 ethnos=A activage=4 arrivalage=29 pgpprac=332b1c9a sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19539 aearrivaltime=.
diag_scheme=710 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=19
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
aekey=96a372e7 procodet=RXF sex=2 ethnos=A activage=9 arrivalage=27 pgpprac=5a1d8e24 sitetret=
aearrivalmode=2 aeattenddisp=6 aerefsource=1 aedepttype=1 aearrivaldate=19533 aearrivaltime=.
diag_scheme=1306 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=3 invest_03=3 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=. treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=20
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1048560 at 13:29   1048500 at 14:13
NOTE: There were 1048575 observations read from the data set WORK.SHEFFIELD.
NOTE: The data set WORK.CONTROL1 has 0 observations and 45 variables.
NOTE: DATA statement used (Total process time):
      real time           0.73 seconds
      cpu time            0.73 seconds

sks521 · Posted 08-14-2019 09:00 AM

So the log below has several errors as you can see;

9    Data control1;
10   set Sheffield;
11   if aekey NE ' ' then visit=1;
12   *with 2-16 year olds with wheese/asthma diagnosis, investigation and treatment codes in the
12 ! rest of Yorkshire & Humber;
13   if arrivalage <2 OR diag_01 - diag_06 NOT in ('251', 'J441', 'J45', 'J459', 'J46', 'J46X',
13 ! 'R062', '7001', '7002', '7003', '7004')
14   or treat_01 -treat_09 NOT in ('25', '40') then delete;
15   if procodet = 'RAE' then group1=1;
16   else group1=0;
17   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      13:21   13:29   13:31   14:13   14:14
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=e81e201e procodet=RXF sex=1 ethnos=A activage=2 arrivalage=73 pgpprac=da4863e9 sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19518 aearrivaltime=.
diag_scheme=1014 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=34 treat_03=41 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=1
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=5221b6af procodet=RXF sex=2 ethnos=J activage=6 arrivalage=82 pgpprac=37d3d778 sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19537 aearrivaltime=.
diag_scheme=1359 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=1 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=221 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=2
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=c5f9d619 procodet=RCB sex=1 ethnos=A activage=. arrivalage=16 pgpprac=b6a4f270
sitetret=RCB55 aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19165
aearrivaltime=. diag_scheme=1602 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=221 treat_03=11 treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=3
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=42bdbdc7 procodet=RWY sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=6868ed2c
sitetret=RWY01 aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19104
aearrivaltime=. diag_scheme=231 diag_01=1 diag_02=05 15L diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=1 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=511 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=4
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=b43c9c60 procodet=RXF sex=2 ethnos=A activage=15 arrivalage=21 pgpprac=b9e9dcb3 sitetret=
aearrivalmode=2 aeattenddisp=1 aerefsource=1 aedepttype=1 aearrivaldate=19526 aearrivaltime=.
diag_scheme=1423 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=511 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=5
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=dd8c3208 procodet=RWA sex=1 ethnos=A activage=16 arrivalage=16 pgpprac=f6b0b1b3
sitetret=RWA00 aearrivalmode=2 aeattenddisp=2 aerefsource=1 aedepttype=1 aearrivaldate=19051
aearrivaltime=. diag_scheme=1123 diag_01=1 diag_02=03 25L diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=3 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=291 treat_03=43 treat_04=21
treat_05=12 treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=6
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=c810915d procodet=RJL sex=1 ethnos=A activage=16 arrivalage=16 pgpprac=V81999
sitetret=RJL32 aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19013
aearrivaltime=. diag_scheme=1601 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=21 treat_04=236
treat_05=42 treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=7
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=4c880879 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=8e29e8d2
sitetret=RP5DR aearrivalmode=2 aeattenddisp=2 aerefsource=8 aedepttype=1 aearrivaldate=19094
aearrivaltime=. diag_scheme=2115 diag_01=1 diag_02=38 diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=99 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=8
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=4ee99a96 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=8e29e8d2
sitetret=RP5DR aearrivalmode=2 aeattenddisp=5 aerefsource=1 aedepttype=1 aearrivaldate=19221
aearrivaltime=. diag_scheme=2029 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=51 treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=9
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=ab1c30f4 procodet=RCD sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=eade35a6
sitetret=RCD00 aearrivalmode=9 aeattenddisp=3 aerefsource=8 aedepttype=1 aearrivaldate=19090
aearrivaltime=. diag_scheme=1933 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=10
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=002c5a7a procodet=RXF sex=1 ethnos=A activage=12 arrivalage=41 pgpprac=e7c400b0 sitetret=
aearrivalmode=2 aeattenddisp=12 aerefsource=1 aedepttype=4 aearrivaldate=19512 aearrivaltime=.
diag_scheme=952 diag_01=1 diag_02=38 diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=27 treat_03=. treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=11
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=dcc81124 procodet=RXF sex=2 ethnos=A activage=7 arrivalage=20 pgpprac=51c5f674 sitetret=
aearrivalmode=2 aeattenddisp=2 aerefsource=0 aedepttype=1 aearrivaldate=19521 aearrivaltime=.
diag_scheme=1126 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=10 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=12
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=7fe0f97f procodet=RXF sex=2 ethnos=A activage=8 arrivalage=88 pgpprac=8d1cfce8 sitetret=
aearrivalmode=1 aeattenddisp=1 aerefsource=3 aedepttype=1 aearrivaldate=19536 aearrivaltime=.
diag_scheme=1755 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=13
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=9c346f3b procodet=RXF sex=1 ethnos=R activage=2 arrivalage=73 pgpprac=1e3bcd6c sitetret=
aearrivalmode=1 aeattenddisp=1 aerefsource=0 aedepttype=1 aearrivaldate=19523 aearrivaltime=.
diag_scheme=1238 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=1 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=14
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=d1787cf8 procodet=RXF sex=2 ethnos=J activage=11 arrivalage=67 pgpprac=2a39f912 sitetret=
aearrivalmode=2 aeattenddisp=4 aerefsource=0 aedepttype=1 aearrivaldate=19513 aearrivaltime=.
diag_scheme=1449 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=15
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=4f0e6017 procodet=RXF sex=2 ethnos=L activage=13 arrivalage=67 pgpprac=2a39f912 sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=7 aedepttype=1 aearrivaldate=19516 aearrivaltime=.
diag_scheme=1133 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=. treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=16
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=de5e1b98 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=50a35e60
sitetret=RP5DR aearrivalmode=1 aeattenddisp=2 aerefsource=1 aedepttype=1 aearrivaldate=19188
aearrivaltime=. diag_scheme=1352 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=1 invest_03=2 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=25 treat_03=12 treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=17
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=3f4118a4 procodet=RP5 sex=2 ethnos=A activage=16 arrivalage=16 pgpprac=50a35e60
sitetret=RP5DR aearrivalmode=1 aeattenddisp=2 aerefsource=8 aedepttype=1 aearrivaldate=19220
aearrivaltime=. diag_scheme=1110 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=
invest_01=  invest_02=2 invest_03=  invest_04=  invest_05=  invest_06=  invest_07=  invest_08=
invest_09=  invest_10=  invest_11=  invest_12=  treat_01=. treat_02=99 treat_03=. treat_04=.
treat_05=  treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=.
_ERROR_=1 _N_=18
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
aekey=cdea9079 procodet=RXF sex=2 ethnos=A activage=4 arrivalage=29 pgpprac=332b1c9a sitetret=
aearrivalmode=2 aeattenddisp=3 aerefsource=1 aedepttype=1 aearrivaldate=19539 aearrivaltime=.
diag_scheme=710 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=24 invest_03=24 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=21 treat_03=222 treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=19
NOTE: Invalid numeric data, 'J441' , at line 13 column 29.
WARNING: Limit set by ERRORS= option reached.  Further errors of this type will not be printed.
aekey=96a372e7 procodet=RXF sex=2 ethnos=A activage=9 arrivalage=27 pgpprac=5a1d8e24 sitetret=
aearrivalmode=2 aeattenddisp=6 aerefsource=1 aedepttype=1 aearrivaldate=19533 aearrivaltime=.
diag_scheme=1306 diag_01=1 diag_02=  diag_03=  diag_04=  diag_05=  diag_06=  invest_01=
invest_02=3 invest_03=3 invest_04=  invest_05=  invest_06=  invest_07=  invest_08=  invest_09=
invest_10=  invest_11=  invest_12=  treat_01=. treat_02=222 treat_03=. treat_04=. treat_05=
treat_06=  treat_07=  treat_08=  treat_09=  imd15decile_overall=. visit=1 group1=. _ERROR_=1
_N_=20
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1048560 at 13:29   1048500 at 14:13
NOTE: There were 1048575 observations read from the data set WORK.SHEFFIELD.
NOTE: The data set WORK.CONTROL1 has 0 observations and 45 variables.
NOTE: DATA statement used (Total process time):
      real time           0.73 seconds
      cpu time            0.73 seconds

Tom · Posted 08-14-2019 10:01 AM

They are pretty clear errors. What do you think they mean?

sks521 · Posted 08-16-2019 04:32 AM

So, I did revise the infile statement as below; but I am getting aearrivaldate in a very strange format like, 19518, 19537 and don't know what's wrong with it

data Sheffield;
infile 'I:\CHCSheffield\20190401ACE-COPY\ACE_AE_20190401_fixed.csv' dsd truncover lrecl=32767 FIRSTOBS=2;
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;

Kurt_Bremser · Posted 08-16-2019 07:31 AM

That's just the raw date values (count of days from 1960-01-01). Assign a proper date format to your variable (eg yymmddd10. to get the notation I used).

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

Tom · Posted 08-14-2019 10:48 AM

Why are you reading some of the TREATxxx variables as numbers and some as character?

The syntax of a length statement is pairs of variable lists and length specifications. To indicate that the variable is character precede the length with a $. SAS stores all numbers as 8 byte floating point values, so it is best to use 8 as the length of numeric variables. You can save some space in the output dataset by using a shorter length, but normally the saving is not worth the risk of accidentally truncating some of the least significant digits from the value.

Your latest error message is saying that you have defined your diagnosis variables as numeric instead of character. Don't do that. First you cannot store the J codes since they don't look like numbers. Plus you will loose the leading zeros on some codes. And if you are keeping the period in the code you could lose the trailing zeros also.

SAS Innovate 2025: Call for Content

Classroom Training Available!