BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DmytroYermak
Lapis Lazuli | Level 10

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Add one more option.

 

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

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

Add one more option.

 

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

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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