DATA Step, Macro, Functions and more

Importing a CSV file using infile statement

Reply
Frequent Contributor
Posts: 138

Importing a CSV file using infile statement

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!

Super User
Posts: 10,516

Re: Importing a CSV file using infile statement

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

Frequent Contributor
Posts: 138

Re: Importing a CSV file using infile statement

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. 

Super User
Posts: 10,516

Re: Importing a CSV file using infile statement


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.

Respected Advisor
Posts: 3,777

Re: Importing a CSV file using infile statement

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.

Super Contributor
Posts: 408

Re: Importing a CSV file using infile statement

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.
Respected Advisor
Posts: 3,777

Re: Importing a CSV file using infile statement

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. 

Super User
Posts: 6,948

Re: Importing a CSV file using infile statement


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,085

Re: Importing a CSV file using infile statement

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.

Ask a Question
Discussion stats
  • 8 replies
  • 1245 views
  • 0 likes
  • 6 in conversation