Import external data

[pradeep_kadasi]

I want to read external file into sas dataset with 1000+ variables.
Proc import is failing due to invalid data in some records.
How to use infile, input statement without having to specify all variable names?

[ballardw]

Any time you get an error or do not understand what your SAS code did you should copy the text from the log of the entire step or procedure including the code and all the note, warning or messages from the LOG. Then on the forum open a text box using the </> icon that appears above the main message window and paste the text. The text box is important to preserve formatting of the text and the diagnostic characters that SAS often supplies with problems as the main message windows on this forum will reformat pasted text.

The description you provide is not a "fail". Invalid data means that proc import, which guesses variable types based on a few lines of data, encountered data differently than in the rows used to create the import.

If you read the LOG enough to see invalid data messages then you have above it, generally, the data step code that was used to read the file. So you can copy that code from the log, paste into the editor and clean up removing line numbers and such. Then make changes, such as INFORMAT statements to address your specific data issues.

Then you don't have to retype all the variable names.

Note: without seeing exactly what invalid data messages you get we can't make any guesses as to what informat or variable attributes need to be addressed and to what.

Also, if you have id fields like account numbers and such that only contain digits then Import will attempt to read those as numeric values. Which means that you lose leading zeros common to some items like bank routing numbers. If the variable is not going to be used in arithmetic it should be character.

[Tom]

You can definitely read in 1000+ variables without a lot of typing.

You could just use a variable list

input v1-v1000 ;

If the first row of the text file has valid variable names you could read those as data

data names ;
  infile 'myfile.txt' dsd obs=1 ;
  varnum+1;
  input name :$32. @@;
run;

And use it to either by generating the INPUT statement. 

filename code temp;
data _null_;
  set names end=eof;
  file code lrecl=75 ;
  if _n_=1 then put 'input '  @;
  put name @;
  if eof then put ';' ;
run;

Or by generating a rename statement.

filename code temp;
data _null_;
  set names end=eof;
  file code lrecl=75 ;
  if _n_=1 then put 'rename '  @;
  put 'v' varnum '=' name @;
  if eof then put ';' ;
run;

If you want another tool to help you analyze the text file and figure out how to define variables and read the file try this macro:

https://github.com/sasutils/macros/blob/master/csv2ds.sas