topic Importing a CSV file using infile statement in SAS Programming

Importing a CSV file using infile statement

Walternate — Thu, 31 Mar 2016 19:34:06 GMT

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!

Re: Importing a CSV file using infile statement

ballardw — Thu, 31 Mar 2016 19:44:12 GMT

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

Re: Importing a CSV file using infile statement

Walternate — Thu, 31 Mar 2016 19:46:40 GMT

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.

Re: Importing a CSV file using infile statement

data_null__ — Fri, 12 Jan 2018 14:06:13 GMT

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.

Re: Importing a CSV file using infile statement

jklaverstijn — Thu, 31 Mar 2016 20:39:52 GMT

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.

Re: Importing a CSV file using infile statement

data_null__ — Thu, 31 Mar 2016 21:14:13 GMT

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.

Re: Importing a CSV file using infile statement

Astounding — Thu, 31 Mar 2016 21:50:04 GMT

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.

Re: Importing a CSV file using infile statement

ballardw — Thu, 31 Mar 2016 22:00:04 GMT

@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.

Re: Importing a CSV file using infile statement

Kurt_Bremser — Fri, 01 Apr 2016 06:00:55 GMT

@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.