Importing a CSV file using infile statement

[Walternate]

Hi, 

I am using the following code to import a CSV file into SAS. The CSV file has an ID variable, which is character, and a numeric variable which is numbers from 1-100 with 2 decimal places. 

This is the code I'm using :

data want;
%let _EFIERR_ = 0;
infile 'filepath\file.csv'
delimiter = ',' DSD MISSOVER lrecl=32767 firstobs=2;
INFORMAT ID  $CHAR6.;
INFORMAT num_var 8.2;

****************************************************************;
FORMAT ID $CHAR6.;
FORMAT num_var 8.2;

INPUT
ID $
num_var;

if _ERROR_ then call symput('_EFIERR_',1);
run;

I keep getting an error message which says Invalid data for my numeric var, which seems to be when it has missing values in the Excel spreadsheet. I thought that the MISSOVER option was supposed to deal with that, but apparently not. Is my formatting off? Any help is much appreciated. 

Thanks!

[ballardw]

Post a few rows of the data that duplicate the problem.

[Walternate]

Additionally, my IDs are 6 characters long (max) but it's only reading 2 or 3 characters in the output data. 

The input data looks like this:

ID       num_var

111     12.83

222     93.21

333       .

From what I can tell from the log, it's the ones where num_var is missing that are causing the error message issue. 

[ballardw]

---

@Walternate wrote:

Additionally, my IDs are 6 characters long (max) but it's only reading 2 or 3 characters in the output data. 

The input data looks like this:

ID       num_var

111     12.83

222     93.21

333       .

From what I can tell from the log, it's the ones where num_var is missing that are causing the error message issue. 

---

Your infile statement says the data is comma delimited but your example doesn't show any comma. I think you need to show the actual log with code and error messages.

[data_null__ · Accepted solution]

Invalid data is data that cannot be read a number by the format in this case 8.2.  You should the output in the log that allow you to see the problem in the data line(s).

Show that part of the log to the group.

@Astounding also points out:

Also, you should know about the 8.2 informat.  If your incoming value is 100, the result will be 1, not 100.  The 8.2 informat expects to find a decimal point.  But if it doesn't find one, it inserts one and puts two digits after the decimal point.

[jklaverstijn]

We would be greatly helped with some sample data. But from what you tell it looks like TRUNCOVER will deal with this rather than MISSOVER.

- Jan.

[data_null__]

TRUNCOVER is not for LIST input it is for formatted or column input.  Although it works the same as MISSOVER for list input.

Truncover was invented for folks who coundn't figure out how to read variable length records. 

[Kurt_Bremser]

---

@data_null__ wrote:

TRUNCOVER is not for LIST input it is for formatted or column input.  Although it works the same as MISSOVER for list input.

Truncover was invented for folks who coundn't figure out how to read variable length records. 

---

Don't be so harsh. TRUNCOVER is needed when reading VB files from z/OS mainframes. All columns except the last one are fixed length, and no delimiters are present. TRUNCOVER allows a simple formatted input statement in such cases.

[Astounding]

It would be helpful to know what is in the incoming data line.  That would explain what's in the invalid data.  Perhaps it's not being treated as a blank but actually contains some other character. 

Also, you should know about the 8.2 informat.  If your incoming value is 100, the result will be 1, not 100.  The 8.2 informat expects to find a decimal point.  But if it doesn't find one, it inserts one and puts two digits after the decimal point.