topic Re: Import csv data as character (how to force) in SAS Programming

Import csv data as character (how to force)

DmytroYermak — Tue, 21 Nov 2017 12:26:00 GMT

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?

Re: Import csv data as character (how to force)

RW9 — Tue, 21 Nov 2017 12:28:27 GMT

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.

Re: Import csv data as character (how to force)

Ksharp — Tue, 21 Nov 2017 12:31:28 GMT

Add one more option.

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

Re: Import csv data as character (how to force)

DmytroYermak — Tue, 21 Nov 2017 12:46:19 GMT

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.

Re: Import csv data as character (how to force)

Kurt_Bremser — Tue, 21 Nov 2017 13:05:00 GMT

@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.