Try using the PAD option on the INFILE statement that points to the file (eg. INFILE '\???\???.XLS' PAD; ) and using the COMMA10.2 informat. The PAD option pads every with blanks so that each line has the same length. For wide files, (record lengths > 80 to 255 depending on version of SAS) you may also need the LRECL= option to specify how long each line needs to be.

The "Mary" record has a length of 38 with NUM2 found in columns 30-38 (30 to 30+9)--Reading this line with COMMA10. causes SAS to try to read beyond the end of the record, causing an error. The "Jack" record has a length of 39 with NUM2 found in columns 30-39 (30 to 30+10)--Using COMMA9. here doesn't read the entire data value.

Alternatives may be to use the TRUNCOVER option on the INFILE statement, use the : (colon) modifier before the informats on the INPUT statement, or to move the numeric informats to an INFORMAT statement.

A side: The SAS code "@8 address $13" contains an error, you should have "$13." . Without the . (period) this section is interpreded as go to column 8 (@8) read a char variable ($) until you reach a space, then advance thirteen (13) more column positions. ADDRESS will contain the street numbers but not the street names.

Tim Pi wrote: > > Hi SAS users, > > I use 'comma' format to read a variable which is at the > end of a record. The data set is saved as a space delimited > text file from Excel. This file is like: > .........+.........+.........+.........+ > Mary 646 Green St. 50.01 556.44 > John 8766 Gray Av. (56.3) 75.98 > Jack 12 Marion Rd. 6.77 (45.62) > > ... > ********* > If I use the following format, (45.62) can not be read in, that is, > the value is missing. > > @1 name $4 > @8 address $13 > @21 num1 comma9.2 > @30 num2 comma9.2; > > ********* > If I use comma10.2 instead, both 556.44 and 75.98 cannot be read in. > > Any idea? > > Thanks! > > Tim