topic Re: Removing linefeeds in SAS Procedures

Removing linefeeds

akotronis — Wed, 15 May 2013 13:49:10 GMT

Hello everybody.

This is my first post.

I have a a2.csv file in which some fields contain linefeeds. I want to make a data set from this with linefeeds removed. The first column has the variables v1 v2 v3 v4, the first numeric and 3 observations.

I have tried the following:

Two approaches:

data trial;

infile 'C:\HRH-C\pet\a2.csv' dsd delimiter=';' firstobs=2;

input v1 v2 $ v3 $ v4 $;

v1= TRANWRD(v1,'0A'x,"");

v2= TRANWRD(v2,'0A'x,"");

v3= TRANWRD(v3,'0A'x,"");

v4= TRANWRD(v4,'0A'x,"");

run;

data trial;

infile 'C:\HRH-C\pet\a2.csv' dsd delimiter=';' firstobs=2;

input v1 v2 $ v3 $ v4 $;

v1=compress(v1,,"kw");

v2=compress(v2,,"kw");

v3=compress(v3,,"kw");

v4=compress(v4,,"kw");

run;

but neither of them seems to work.

If instead of a linefeed I put another character in the csv file and use the corresponding first version, it works.

Any ideas? (I would like to try the ascii code 10 for the linefeed too but I don't know the syntax for the ascii code..)

Thank you in advance

Message was edited by: Anastasios Kotronis

Re: Removing linefeeds

TimArm — Wed, 15 May 2013 15:30:55 GMT

Try using

v1= TRANSLATE(v1,"",'0A'x);

The function TRANWRD looks for white space to delimit words - so probably considers '0A'x as white space.

NOTE: the arguments to TRANSLATE need to be swapped around from TRANWRD.

Tim

Re: Removing linefeeds

Tom — Wed, 15 May 2013 15:47:54 GMT

Hexadecimal notation is '10'x .

If the file was created on Unix there will not be an easy way to distinguish the embedded LFs from the ones that are used to mark the end of the line.

If the file was created on Windows/DOS then you could try adding the TERMSTR=CRLF option to your INFILE statement.

Re: Removing linefeeds

akotronis — Wed, 15 May 2013 16:39:28 GMT

I found TERMSTR=CRL, as you suggest, just before TimArm answered, here (How to import multiline CSV in SAS - Stack Overflow) and it worked just fine. Thank you both for your time (by the way, the TRANSLATE suggestion doesn't work neither. I don't why, but, as for the COMPREES case, from what I concluded by what I found on the web, it seems to work if you already have your data in a sas data set.)

Re: Removing linefeeds

jaredp — Fri, 17 May 2013 19:25:55 GMT

I've had run-ins with CR and LF as well, so I thought I'd chime in even though you found your solution.

This solution may provide insight: http://support.sas.com/kb/26/065.html

And this code may help too: http://studysas.blogspot.ca/2011/10/how-to-remove-carriage-return-and.html

If this is a one-time occurence, you can use the open source software Notepad++ to quickly convert the file to the "Classic Mac" format. It will convert all Line Feeds LF to Carriage Returns CR. (Apparently newer Macs (not "Classic" Macs) now use LF for end of line EOL.)

Or, if you have MS Excel, you can open up your CSV and use this VB macro to remove Line Feeds.

Sub CleanCells()

'Strips special characters (line feeds, etc.) from all selected cells

Dim Cell As Range

For Each Cell In Selection

Cell.Value = WorksheetFunction.Clean(Cell.Value)

Next Cell

End Sub

If you are getting your data file from an FTP server to a Windows computer and you know the file on the server does not contain LF, then be sure to download the file using binary transfer mode and not ASCII. This will retain the CR and not add LF.

Re: Removing linefeeds

akotronis — Sat, 18 May 2013 01:10:34 GMT

Thanks for your time. In excel an easier way to remove the line feeds would be =substitute(a2;char(10);"") assuming the cell with the linefeed is A2.