Help using Base SAS procedures

Removing linefeeds

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Removing linefeeds

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


Accepted Solutions
Solution
‎05-15-2013 11:47 AM
Super User
Super User
Posts: 6,137

Re: Removing linefeeds

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.

View solution in original post


All Replies
Contributor
Posts: 29

Re: Removing linefeeds

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

Solution
‎05-15-2013 11:47 AM
Super User
Super User
Posts: 6,137

Re: Removing linefeeds

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.

Occasional Contributor
Posts: 12

Re: Removing linefeeds

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

Contributor
Posts: 71

Re: Removing linefeeds

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.

Occasional Contributor
Posts: 12

Re: Removing linefeeds

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 2157 views
  • 5 likes
  • 4 in conversation