BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
akotronis
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
TimArm
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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.

akotronis
Calcite | Level 5

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

jaredp
Quartz | Level 8

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.

akotronis
Calcite | Level 5

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 8470 views
  • 5 likes
  • 4 in conversation