DATA Step, Macro, Functions and more

Import csv data as character (how to force)

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Import csv data as character (how to force)

[ Edited ]

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? 


Accepted Solutions
Solution
‎11-21-2017 07:44 AM
Super User
Posts: 10,700

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

Posted in reply to DmytroYermak

Add one more option.

 

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,456

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

Posted in reply to DmytroYermak

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.

Solution
‎11-21-2017 07:44 AM
Super User
Posts: 10,700

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

Posted in reply to DmytroYermak

Add one more option.

 

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

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

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.

Super User
Posts: 9,940

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

[ Edited ]
Posted in reply to DmytroYermak

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 481 views
  • 0 likes
  • 4 in conversation