DATA Step, Macro, Functions and more

Problem while importing CSV file.

Reply
Occasional Contributor
Posts: 15

Problem while importing CSV file.

Hi,

When I am importing CSV file into SAS , Junk value is getting inserted in last coloumn of every record. Please let me know if there is any option which remove junk values while importing. I am using Infile statement with following Options

dlm=';' TRUNCOVER MISSOVER DSD FIRSTOBS=2

Thanks
Trusted Advisor
Posts: 2,115

Re: Problem while importing CSV file.

Looks like you are using the DATA step for the import.

Generally, this sort of thing is an indication of a problem in the incoming data stream. You will need to do some detective work. The first ones that I might look at are

-- do you have "junk" at the end of the incoming record? You should use a hex editor to look at the data line as there may be some non-display characters.
-- did the .CSV file come from a different environment? For instance, if it was created on a PC and you used a binary FTP transfer to get it to Unix, then the End-Of-Line character will be wrong and may cause errors. Ditto the other way.

Doc Muhlbaier
Duke
Valued Guide
Posts: 2,177

Re: Problem while importing CSV file.

Doc will be right! As the junk is at the end of the last field, it will normally be part of the end-of-line string.
In SAS92 infile option TERMSTR= solves the problem (like termstr=crlf)
Before SAS92, just add the rogue character to the DLM= value
infile ghjfghj dsd dlm='3b0a0d'x ....etcetera... ;
(where 3b is the hex value for semi-colons on most platforms)
I added 0A0Dx among delimiters on an "all-inclusive" approach. If normally treated as line-end characters, then they won't arrive in the infile buffer, but when they do, they'll be excluded from data values.
Ask a Question
Discussion stats
  • 2 replies
  • 239 views
  • 0 likes
  • 3 in conversation