BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Walternate
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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.

View solution in original post

8 REPLIES 8
ballardw
Super User

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

Walternate
Obsidian | Level 7

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
Super User

@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__
Jade | Level 19

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
Rhodochrosite | Level 12
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__
Jade | Level 19

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
Super User

@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
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 22381 views
  • 1 like
  • 6 in conversation