Help using Base SAS procedures

Invalid data using PROC IMPORT

Reply
N/A
Posts: 0

Invalid data using PROC IMPORT

When I tried to import a .csv file with many variables using PROC IMPORT, the log window shows Invalid Data for one variable. Is this a format/informat problem? How can I address the issue by successfully importing the data? Can I delete the variable somewhere before I import it to SAS? I really don't want to delete the variable though. This is a very big dataset and I doubt that Excel can handle it.

I changed the default setting about how many records will be read before the procedure decides on the data format, but not sure if it'll work since I'm still waiting for the datasets to be read.
Super Contributor
Super Contributor
Posts: 3,174

Re: Invalid data using PROC IMPORT

Posted in reply to deleted_user
It should be quite clear from the SAS log about what input record (SAS diagnostic variable _N_) and so you can review your input file to validate the input as either NUMERIC or CHARACTER.

Also, the SAS log will indicate the SAS-generated code with the INPUT statement and INFORMAT used for each field.

Scott Barry
SBBWorks, Inc.
Super User
Posts: 11,343

Re: Invalid data using PROC IMPORT

Posted in reply to deleted_user
While Proc Import can be very useful it does have limitations when the data isn't especially well formed. For example many types of ID fields such as product 'numbers' or ZIP codes may have mostly values that may be interpretted as numerics but have exceptions. The program will look at a number of lines to try to guess whether a variable should be string or numeric but may not look far enough if you have large data sets.

I would recommend running proc import and then looking at the generated code in the log. If any of the variables are numeric, informat is $xx., then copy the code out of the log into the editor and change the informat, format and input statements for that variable.
Ask a Question
Discussion stats
  • 2 replies
  • 347 views
  • 0 likes
  • 3 in conversation