topic Re: Importing an Excel File into SAS in SAS Procedures

Importing an Excel File into SAS

stancemcgraw — Tue, 22 Mar 2016 15:24:31 GMT

I am trying to import an excel file. I first saved it as a CSV file on my computer and then input the following code into SAS 9.3:

Data TAI;

infile 'C:\SAS\TAI.csv' dlm=',' firstobs=2;

input strokeid prepost arrdate $ year prepost15 age sex $ race $ ttn nihss sta ptr dtc ctp lastmRSscore;

run;

I received the following errors:

NOTE: The infile 'C:\SAS\IAT.csv' is:

Filename=C:\SAS\IAT.csv,

RECFM=V,LRECL=256,File Size (bytes)=22665,

Last Modified=22Mar2016:09:15:55,

Create Time=22Mar2016:09:16:52

NOTE: Invalid data for strokeid in line 2 1-14.

NOTE: Invalid data for ctp in line 3 1-13.

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

3 13074*SWEDISH,1,3-Jan-12,2012,1,83,M,W,0,6,122,,15,,2 53

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=3-Jan-12 year=2012 prepost15=1 age=60 sex=F race=W ttn=499 nihss=18 sta=116 ptr=46 dtc=2 ctp=.

lastmRS=1 _ERROR_=1 _N_=1

NOTE: Invalid data for strokeid in line 4 1-13.

NOTE: Invalid data for ctp in line 5 1-13.

5 13117*SWEDISH,1,13-Jan-12,2012,1,52,M,W,0,17,229,,12,,6 55

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=9-Jan-12 year=2012 prepost15=1 age=65 sex=M race=W ttn=10 nihss=14 sta=219 ptr=15 dtc=3 ctp=.

lastmRS=1 _ERROR_=1 _N_=2

NOTE: Invalid data for strokeid in line 6 1-13.

NOTE: Invalid data for ctp in line 7 1-13.

7 13202*SWEDISH,1,7-Feb-12,2012,1,48,F,W,19,16,236,,33,,4 55

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=19-Jan-1 year=2012 prepost15=1 age=72 sex=F race=W ttn=8 nihss=13 sta=317 ptr=16 dtc=5 ctp=.

lastmRS=1 _ERROR_=1 _N_=3

NOTE: Invalid data for strokeid in line 8 1-13.

NOTE: Invalid data for ctp in line 9 1-13.

9 13237*SWEDISH,1,14-Feb-12,2012,1,50,F,o,15,12,247,,21,,2 56

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=7-Feb-12 year=2012 prepost15=1 age=83 sex=F race=W ttn=3 nihss=20 sta=252 ptr=17 dtc=4 ctp=.

lastmRS=1 _ERROR_=1 _N_=4

NOTE: Invalid data for strokeid in line 10 1-13.

NOTE: Invalid data for ctp in line 11 1-13.

11 13247*SWEDISH,1,16-Feb-12,2012,1,86,F,o,68,12,4,,78,,3 54

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=15-Feb-1 year=2012 prepost15=1 age=62 sex=M race=W ttn=0 nihss=12 sta=281 ptr=21 dtc=0 ctp=.

lastmRS=1 _ERROR_=1 _N_=5

NOTE: Invalid data for strokeid in line 12 1-13.

NOTE: Invalid data for ctp in line 13 1-13.

13 13257*SWEDISH,1,20-Feb-12,2012,1,60,M,W,0,4,221,,15,,2 54

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=19-Feb-1 year=2012 prepost15=1 age=81 sex=M race=W ttn=0 nihss=20 sta=144 ptr=21 dtc=2 ctp=.

lastmRS=1 _ERROR_=1 _N_=6

NOTE: Invalid data for strokeid in line 14 1-13.

NOTE: Invalid data for ctp in line 15 1-13.

15 13323*SWEDISH,1,4-Mar-12,2012,1,77,F,W,0,26,299,82,14,49,6 58

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=22-Feb-1 year=2012 prepost15=1 age=63 sex=M race=W ttn=0 nihss=23 sta=389 ptr=16 dtc=6 ctp=.

lastmRS=1 _ERROR_=1 _N_=7

NOTE: Invalid data for strokeid in line 16 1-13.

16 13351*SWEDISH,1,10-Mar-12,2012,1,77,F,W,2,19,118,82,17,69,4 59

strokeid=. prepost=1 arrdate=10-Mar-1 year=2012 prepost15=1 age=77 sex=F race=W ttn=2 nihss=19 sta=118 ptr=82 dtc=17 ctp=69

lastmRS=4 _ERROR_=1 _N_=8

NOTE: Invalid data for strokeid in line 17 1-13.

17 13376*SWEDISH,1,14-Mar-12,2012,1,42,F,o,0,27,249,29,1,46,2 58

strokeid=. prepost=1 arrdate=14-Mar-1 year=2012 prepost15=1 age=42 sex=F race=o ttn=0 nihss=27 sta=249 ptr=29 dtc=1 ctp=46

lastmRS=2 _ERROR_=1 _N_=9

NOTE: Invalid data for strokeid in line 18 1-13.

18 13414*SWEDISH,1,20-Mar-12,2012,1,73,F,W,0,20,1051,49,19,33,2 60

strokeid=. prepost=1 arrdate=20-Mar-1 year=2012 prepost15=1 age=73 sex=F race=W ttn=0 nihss=20 sta=1051 ptr=49 dtc=19 ctp=33

lastmRS=2 _ERROR_=1 _N_=10

NOTE: Invalid data for strokeid in line 19 1-13.

19 13425*SWEDISH,1,22-Mar-12,2012,1,82,F,W,0,22,185,81,19,43,1 59

strokeid=. prepost=1 arrdate=22-Mar-1 year=2012 prepost15=1 age=82 sex=F race=W ttn=0 nihss=22 sta=185 ptr=81 dtc=19 ctp=43

lastmRS=1 _ERROR_=1 _N_=11

NOTE: Invalid data for strokeid in line 20 1-13.

NOTE: Invalid data for ctp in line 21 1-13.

21 13450*SWEDISH,1,28-Mar-12,2012,1,86,F,W,0,18,160,,17,23,3 57

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=26-Mar-1 year=2012 prepost15=1 age=60 sex=M race=W ttn=0 nihss=17 sta=549 ptr=11 dtc=3 ctp=.

lastmRS=1 _ERROR_=1 _N_=12

NOTE: Invalid data for strokeid in line 22 1-13.

22 13462*SWEDISH,1,28-Mar-12,2012,1,85,F,W,4,19,251,93,22,82,3 59

strokeid=. prepost=1 arrdate=28-Mar-1 year=2012 prepost15=1 age=85 sex=F race=W ttn=4 nihss=19 sta=251 ptr=93 dtc=22 ctp=82

lastmRS=3 _ERROR_=1 _N_=13

NOTE: Invalid data for strokeid in line 23 1-13.

NOTE: Invalid data for lastmRS in line 24 1-13.

24 13469*SWEDISH,1,31-Mar-12,2012,1,89,M,o,12,,769,180,43,27,6 59

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=29-Mar-1 year=2012 prepost15=1 age=60 sex=F race=W ttn=0 nihss=13 sta=236 ptr=11 dtc=146 ctp=1

lastmRS=. _ERROR_=1 _N_=14

NOTE: Invalid data for strokeid in line 25 1-13.

NOTE: Invalid data for dtc in line 26 1-13.

NOTE: Invalid data for lastmRS in line 26 17-24.

26 13486*SWEDISH,1,4-Apr-12,2012,1,66,F,W,0,25,162,80,,,4 54

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=2-Apr-12 year=2012 prepost15=1 age=41 sex=M race=W ttn=2 nihss=565 sta=112 ptr=2 dtc=. ctp=1

lastmRS=. _ERROR_=1 _N_=15

NOTE: Invalid data for strokeid in line 27 1-13.

27 13488*SWEDISH,1,4-Apr-12,2012,1,64,F,W,0,12,152,43,16,54,6 58

strokeid=. prepost=1 arrdate=4-Apr-12 year=2012 prepost15=1 age=64 sex=F race=W ttn=0 nihss=12 sta=152 ptr=43 dtc=16 ctp=54

lastmRS=6 _ERROR_=1 _N_=16

NOTE: Invalid data for strokeid in line 28 1-13.

NOTE: Invalid data for ctp in line 29 1-13.

29 13515*SWEDISH,1,11-Apr-12,2012,1,61,M,W,0,7,334,81,13,16,3 58

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=9-Apr-12 year=2012 prepost15=1 age=79 sex=M race=W ttn=4 nihss=22 sta=205 ptr=82 dtc=1 ctp=.

lastmRS=1 _ERROR_=1 _N_=17

NOTE: Invalid data for strokeid in line 30 1-13.

NOTE: Invalid data for lastmRS in line 31 1-13.

31 13540*SWEDISH,1,15-Apr-12,2012,1,64,F,o,0,18,32,92,5,162,6 58

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=13-Apr-1 year=2012 prepost15=1 age=54 sex=F race=W ttn=0 nihss=13 sta=262 ptr=14 dtc=81 ctp=3

lastmRS=. _ERROR_=1 _N_=18

NOTE: Invalid data for strokeid in line 32 1-13.

NOTE: Invalid data for dtc in line 33 1-13.

NOTE: Invalid data for lastmRS in line 33 17-25.

33 13555*SWEDISH,1,19-Apr-12,2012,1,50,F,W,0,15,697,129,14,27,2 60

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=17-Apr-1 year=2012 prepost15=1 age=54 sex=F race=W ttn=18 nihss=548 sta=101 ptr=1 dtc=. ctp=1

lastmRS=. _ERROR_=1 _N_=19

NOTE: Invalid data for strokeid in line 34 1-13.

NOTE: Invalid data for dtc in line 35 1-13.

NOTE: Invalid data for lastmRS in line 35 17-25.

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

35 13653*SWEDISH,1,14-May-12,2012,1,70,F,o,0,34,157,93,20,6,6 58

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

strokeid=. prepost=1 arrdate=8-May-12 year=2012 prepost15=1 age=58 sex=F race=W ttn=6 nihss=1060 sta=51 ptr=3 dtc=. ctp=1

lastmRS=. _ERROR_=1 _N_=20

NOTE: 380 records were read from the infile 'C:\SAS\IAT.csv'.

The minimum record length was 48.

The maximum record length was 61.

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

NOTE: The data set WORK.IAT has 247 observations and 15 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

This continues throughout the data. Does anyone know how to correc this or what this means?

Re: Importing an Excel File into SAS

RW9 — Tue, 22 Mar 2016 15:33:41 GMT

It would be easier if you posted, just the first line or two of the file so we can test it our end. First thing I would do to add:

data tai;
  infile 'C:\SAS\TAI.csv' dlm=',' firstobs=2 lrecl=32767;
  input strokeid prepost arrdate $ year prepost15 age sex $ race $ ttn nihss sta ptr dtc ctp lastmRSscore;
run;

The lrecl is explained here:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003160472.htm

Re: Importing an Excel File into SAS

stancemcgraw — Tue, 22 Mar 2016 15:37:54 GMT

strokeid

prepost

arrdate

year

prepost15

age

sex

race

ttn

nihss

sta

ptr

dtc

ctp

lastmRs

170901*SWEDISH

3-Jan-12

2012

499

116

Re: Importing an Excel File into SAS

stancemcgraw — Tue, 22 Mar 2016 15:46:25 GMT

Is the # 32767 after lrecl an arbitrary number?

Re: Importing an Excel File into SAS

RW9 — Tue, 22 Mar 2016 15:50:13 GMT

It is the maximum value you can have for a row length. Although you shoud never have data getting to that kind of length.

Re: Importing an Excel File into SAS

stancemcgraw — Tue, 22 Mar 2016 16:03:24 GMT

7 data IAT;
8 infile 'C:\SAS\IAT.csv' dlm=',' firstobs=2 lrecl=32767;
9 input strokeid prepost arrdate $ year prepost15 age sex $ race $ ttn nihss sta ptr dtc ctp lastmRSscore;
10 run;

NOTE: The infile 'C:\SAS\IAT.csv' is:
Filename=C:\SAS\IAT.csv,
RECFM=V,LRECL=32767,File Size (bytes)=22665,
Last Modified=22Mar2016:09:15:55,
Create Time=22Mar2016:09:16:52

NOTE: Invalid data for strokeid in line 2 1-14.
NOTE: Invalid data for ctp in line 3 1-13.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
3 13074*SWEDISH,1,3-Jan-12,2012,1,83,M,W,0,6,122,,15,,2 53
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=3-Jan-12 year=2012 prepost15=1 age=60 sex=F race=W ttn=499 nihss=18 sta=116 ptr=46 dtc=2 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=1
NOTE: Invalid data for strokeid in line 4 1-13.
NOTE: Invalid data for ctp in line 5 1-13.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
5 13117*SWEDISH,1,13-Jan-12,2012,1,52,M,W,0,17,229,,12,,6 55
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=9-Jan-12 year=2012 prepost15=1 age=65 sex=M race=W ttn=10 nihss=14 sta=219 ptr=15 dtc=3 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=2
NOTE: Invalid data for strokeid in line 6 1-13.
NOTE: Invalid data for ctp in line 7 1-13.
7 13202*SWEDISH,1,7-Feb-12,2012,1,48,F,W,19,16,236,,33,,4 55
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=19-Jan-1 year=2012 prepost15=1 age=72 sex=F race=W ttn=8 nihss=13 sta=317 ptr=16 dtc=5 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=3
NOTE: Invalid data for strokeid in line 8 1-13.
NOTE: Invalid data for ctp in line 9 1-13.
9 13237*SWEDISH,1,14-Feb-12,2012,1,50,F,o,15,12,247,,21,,2 56
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=7-Feb-12 year=2012 prepost15=1 age=83 sex=F race=W ttn=3 nihss=20 sta=252 ptr=17 dtc=4 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=4
NOTE: Invalid data for strokeid in line 10 1-13.
NOTE: Invalid data for ctp in line 11 1-13.
11 13247*SWEDISH,1,16-Feb-12,2012,1,86,F,o,68,12,4,,78,,3 54
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=15-Feb-1 year=2012 prepost15=1 age=62 sex=M race=W ttn=0 nihss=12 sta=281 ptr=21 dtc=0 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=5
NOTE: Invalid data for strokeid in line 12 1-13.
NOTE: Invalid data for ctp in line 13 1-13.
13 13257*SWEDISH,1,20-Feb-12,2012,1,60,M,W,0,4,221,,15,,2 54
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=19-Feb-1 year=2012 prepost15=1 age=81 sex=M race=W ttn=0 nihss=20 sta=144 ptr=21 dtc=2 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=6
NOTE: Invalid data for strokeid in line 14 1-13.
NOTE: Invalid data for ctp in line 15 1-13.
15 13323*SWEDISH,1,4-Mar-12,2012,1,77,F,W,0,26,299,82,14,49,6 58
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=22-Feb-1 year=2012 prepost15=1 age=63 sex=M race=W ttn=0 nihss=23 sta=389 ptr=16 dtc=6 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=7
NOTE: Invalid data for strokeid in line 16 1-13.
16 13351*SWEDISH,1,10-Mar-12,2012,1,77,F,W,2,19,118,82,17,69,4 59
strokeid=. prepost=1 arrdate=10-Mar-1 year=2012 prepost15=1 age=77 sex=F race=W ttn=2 nihss=19 sta=118 ptr=82 dtc=17 ctp=69
lastmRSscore=4 _ERROR_=1 _N_=8
NOTE: Invalid data for strokeid in line 17 1-13.
17 13376*SWEDISH,1,14-Mar-12,2012,1,42,F,o,0,27,249,29,1,46,2 58
strokeid=. prepost=1 arrdate=14-Mar-1 year=2012 prepost15=1 age=42 sex=F race=o ttn=0 nihss=27 sta=249 ptr=29 dtc=1 ctp=46
lastmRSscore=2 _ERROR_=1 _N_=9
NOTE: Invalid data for strokeid in line 18 1-13.
18 13414*SWEDISH,1,20-Mar-12,2012,1,73,F,W,0,20,1051,49,19,33,2 60
strokeid=. prepost=1 arrdate=20-Mar-1 year=2012 prepost15=1 age=73 sex=F race=W ttn=0 nihss=20 sta=1051 ptr=49 dtc=19 ctp=33
lastmRSscore=2 _ERROR_=1 _N_=10
NOTE: Invalid data for strokeid in line 19 1-13.
19 13425*SWEDISH,1,22-Mar-12,2012,1,82,F,W,0,22,185,81,19,43,1 59
strokeid=. prepost=1 arrdate=22-Mar-1 year=2012 prepost15=1 age=82 sex=F race=W ttn=0 nihss=22 sta=185 ptr=81 dtc=19 ctp=43
lastmRSscore=1 _ERROR_=1 _N_=11
NOTE: Invalid data for strokeid in line 20 1-13.
NOTE: Invalid data for ctp in line 21 1-13.
21 13450*SWEDISH,1,28-Mar-12,2012,1,86,F,W,0,18,160,,17,23,3 57
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=26-Mar-1 year=2012 prepost15=1 age=60 sex=M race=W ttn=0 nihss=17 sta=549 ptr=11 dtc=3 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=12
NOTE: Invalid data for strokeid in line 22 1-13.
22 13462*SWEDISH,1,28-Mar-12,2012,1,85,F,W,4,19,251,93,22,82,3 59
strokeid=. prepost=1 arrdate=28-Mar-1 year=2012 prepost15=1 age=85 sex=F race=W ttn=4 nihss=19 sta=251 ptr=93 dtc=22 ctp=82
lastmRSscore=3 _ERROR_=1 _N_=13
NOTE: Invalid data for strokeid in line 23 1-13.
NOTE: Invalid data for lastmRSscore in line 24 1-13.
24 13469*SWEDISH,1,31-Mar-12,2012,1,89,M,o,12,,769,180,43,27,6 59
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=29-Mar-1 year=2012 prepost15=1 age=60 sex=F race=W ttn=0 nihss=13 sta=236 ptr=11 dtc=146 ctp=1
lastmRSscore=. _ERROR_=1 _N_=14
NOTE: Invalid data for strokeid in line 25 1-13.
NOTE: Invalid data for dtc in line 26 1-13.
NOTE: Invalid data for lastmRSscore in line 26 17-24.
26 13486*SWEDISH,1,4-Apr-12,2012,1,66,F,W,0,25,162,80,,,4 54
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=2-Apr-12 year=2012 prepost15=1 age=41 sex=M race=W ttn=2 nihss=565 sta=112 ptr=2 dtc=. ctp=1
lastmRSscore=. _ERROR_=1 _N_=15
NOTE: Invalid data for strokeid in line 27 1-13.
27 13488*SWEDISH,1,4-Apr-12,2012,1,64,F,W,0,12,152,43,16,54,6 58
strokeid=. prepost=1 arrdate=4-Apr-12 year=2012 prepost15=1 age=64 sex=F race=W ttn=0 nihss=12 sta=152 ptr=43 dtc=16 ctp=54
lastmRSscore=6 _ERROR_=1 _N_=16
NOTE: Invalid data for strokeid in line 28 1-13.
NOTE: Invalid data for ctp in line 29 1-13.
29 13515*SWEDISH,1,11-Apr-12,2012,1,61,M,W,0,7,334,81,13,16,3 58
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=9-Apr-12 year=2012 prepost15=1 age=79 sex=M race=W ttn=4 nihss=22 sta=205 ptr=82 dtc=1 ctp=.
lastmRSscore=1 _ERROR_=1 _N_=17
NOTE: Invalid data for strokeid in line 30 1-13.
NOTE: Invalid data for lastmRSscore in line 31 1-13.
31 13540*SWEDISH,1,15-Apr-12,2012,1,64,F,o,0,18,32,92,5,162,6 58
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=13-Apr-1 year=2012 prepost15=1 age=54 sex=F race=W ttn=0 nihss=13 sta=262 ptr=14 dtc=81 ctp=3
lastmRSscore=. _ERROR_=1 _N_=18
NOTE: Invalid data for strokeid in line 32 1-13.
NOTE: Invalid data for dtc in line 33 1-13.
NOTE: Invalid data for lastmRSscore in line 33 17-25.
33 13555*SWEDISH,1,19-Apr-12,2012,1,50,F,W,0,15,697,129,14,27,2 60
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=17-Apr-1 year=2012 prepost15=1 age=54 sex=F race=W ttn=18 nihss=548 sta=101 ptr=1 dtc=. ctp=1
lastmRSscore=. _ERROR_=1 _N_=19
NOTE: Invalid data for strokeid in line 34 1-13.
NOTE: Invalid data for dtc in line 35 1-13.
NOTE: Invalid data for lastmRSscore in line 35 17-25.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.
35 13653*SWEDISH,1,14-May-12,2012,1,70,F,o,0,34,157,93,20,6,6 58
NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=8-May-12 year=2012 prepost15=1 age=58 sex=F race=W ttn=6 nihss=1060 sta=51 ptr=3 dtc=. ctp=1
lastmRSscore=. _ERROR_=1 _N_=20
NOTE: 380 records were read from the infile 'C:\SAS\IAT.csv'.
The minimum record length was 48.
The maximum record length was 61.
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.IAT has 247 observations and 15 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

Re: Importing an Excel File into SAS

Reeza — Tue, 22 Mar 2016 16:08:10 GMT

StrokeID is not numeric, it cntains character values, so place a $ after strokeid on the input statement

Re: Importing an Excel File into SAS

stancemcgraw — Tue, 22 Mar 2016 16:10:12 GMT

It's still giving me the same errors..

Re: Importing an Excel File into SAS

Reeza — Tue, 22 Mar 2016 16:14:20 GMT

You show 2 as the last variable but SAS thinks its 2 53

Add the truncover option to your infile statement as well

If a suggestion doesnt work please post the code used and the first portion of the log...it starts to repeat itself.

Re: Importing an Excel File into SAS

Reeza — Tue, 22 Mar 2016 16:26:41 GMT

LRECL should be the length of your line of data, but if correct input/infield statement are specified setting it to a large number avoids errors.

Please add Truncover to your infile statement.

Re: Importing an Excel File into SAS

Kurt_Bremser — Tue, 22 Mar 2016 16:28:24 GMT

- you get the message that SAS reached past the end of the line; this means you tried to read more variables than data columns are present in the infile. Count the columns in the infile (# of delimiters + 1) and adjust your input statement accordingly

- if the last column is sometimes empty, use one of the relevant options (MISSOVER or TRUNCOVER) to avoid skipping to the next line

- your input lines fit easily into one screen line, so the lrecl=32767 is not necessary

- all other error messages com from trying to read character data as numeric. Add a dollar sign (as others suggested) after each variable in the input statement that causes errors

- carefully study the log. You can see which data ended up in which variable, so this helps in detecting incorrect order/number of variables in the input statement

Being able to read and interpret the log is possibly the most valuable skill when working with SAS programs.

Re: Importing an Excel File into SAS

ballardw — Tue, 22 Mar 2016 16:41:03 GMT

Since you get values like: 13074*SWEDISH for StrokeId you will want to insure that the length will read the whole value.
Add: Informat StrokeId $15. ; before the Input statement. If you see truncated values for StrokeId make the informat longer.

Re: Importing an Excel File into SAS

Kurt_Bremser — Tue, 22 Mar 2016 16:43:17 GMT

This extract from the log is puzzling me:

NOTE: Invalid data for strokeid in line 2 1-14.
NOTE: Invalid data for ctp in line 3 1-13.

You get a message for line 2 AND 3 while trying to read the first record

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
3 13074*SWEDISH,1,3-Jan-12,2012,1,83,M,W,0,6,122,,15,,2 53

You see that the input line is considerably shorter than 100 bytes

NOTE: Invalid data errors for file ''C:\SAS\IAT.csv'' occurred outside the printed range.
NOTE: Increase available buffer lines with the INFILE n= option.
strokeid=. prepost=1 arrdate=3-Jan-12 year=2012 prepost15=1 age=60 sex=F race=W ttn=499 nihss=18 sta=116 ptr=46 dtc=2 ctp=.

Up to prepost15, the variables correspond to the data shown, but every variable after that seems to have values from another line.

My advice: open the file with a good text editor (like notepad++ or ultraedit) and have a look at the characters (use hex mode, if necessary)

Re: Importing an Excel File into SAS

Kurt_Bremser — Tue, 22 Mar 2016 16:48:14 GMT

And still another one: since you have consecutive delimiters, add the DSD option to the INFILE statement.

Re: Importing an Excel File into SAS

FreelanceReinh — Tue, 22 Mar 2016 22:55:27 GMT

Hi @stancemcgraw,

I think @Kurt_Bremser's latest suggestion (DSD) will improve things considerably. Without DSD the values after the first missing value in a record were read into the wrong variables. Therefore, SAS "went to a new line" and tried to read the values of, e.g., ctp and lastmRSscore from the first two fields of the next record. The non-numeric stroke ID values found there were "Invalid data" for numeric variable ctp as they were initially for strokeid.

Needless to say that @ballardw's important recommendation applies to other character variables as well. In particular, it applies to arrdate, for which truncated date values such as "19-Jan-1" are already documented in the log, because you didn't specify a length ($9) nor an informat ($9.) for this variable. Actually, I would rather recommend to store SAS date values in this variable, i.e., to replace arrdate $ by arrdate :date. in the INPUT statement and to format this variable, e.g., with DATE7. or your favorite date format.

The "puzzling" references to lines 2 and 3 for the "first" record were due to firstobs=2 and "SAS went to a new line" (due to omitting DSD), respectively.

What looks like odd values "2 53" in the log is just the last value of the record (2), followed by the length of the current record (53).