topic Re: Problem while importing CSV file. in SAS Programming

Problem while importing CSV file.

SASACC — Thu, 01 Jul 2010 14:56:57 GMT

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

Re: Problem while importing CSV file.

Doc_Duke — Thu, 01 Jul 2010 17:16:30 GMT

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

Re: Problem while importing CSV file.

Peter_C — Fri, 02 Jul 2010 13:26:50 GMT

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.