Import csv data as character (how to force)

[DmytroYermak]

Hi all,

I have faced with one error when importing csv.

proc import out= WORK.LABDATA
            datafile = "lab.csv"
            dbms = CSV REPLACE ;
			delimiter='|';
run;

One column was interpreted as numeric but it is character. I received the following error:

NOTE: Invalid data for PK_DATE in line 169 29-35.
NOTE: Invalid data for PK_TIME in line 169 37-45.

Is it possible to define the type of the column when reading CSV formatted data? 

[RW9]

Yes, using proc import doesn't really allow for this, it is a guessing procedure.  Take the code which is generated by proc import - you should see this in the log, and then modify and run that instead.  It will look like:

data want;
  infile "yourfile.csv" dlm=",";
  length vara $10;
  informat vara $10;
  format vara $10;
  input vara $;
run;

With this you can set lengths, informats, formats, for each of the variables you read in.

[Ksharp · Accepted solution]

Add one more option.

proc import out= WORK.LABDATA
            datafile = "lab.csv"
            dbms = CSV REPLACE ;
			delimiter='|';
guessingrows=32767;
run;

[DmytroYermak]

I have tried both methods but the second one resoled the issue a little bit faster. Thank you! 

Just one note: I have to remember further in my program that all the variables are character.

[Kurt_Bremser]

---

@DmytroYermak wrote:

I have tried both methods but the second one resoled the issue a little bit faster. Thank you! 

Just one note: I have to remember further in my program that all the variables are character.

---

And if you follow the right path and write your own data step, you can read as character into a temporary variable, and deal with the invalid entries by setting the target variable to missing.

Take control. Also see Maxims 22 & 31.