sas dataset exported to csv file: lay-out corrupt because of hard return

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

sas dataset exported to csv file: lay-out corrupt because of hard return

I have a sas dataset that I copy to a csv file with proc export (SAS 9.1).

Short_Decr is a long string in one variable

dataset.JPG

But in the csv file this variable is split over several columns and lines.

excel.JPG

I found out that this is because Short_Descr contains a hard return. I saw this in Word.

word.JPG

How can I delete this sign from my sas dataset so that my csv file is not corrupt?


Accepted Solutions
Solution
‎09-19-2012 03:42 AM
Occasional Contributor
Posts: 12

Re: sas dataset exported to csv file: lay-out corrupt because of hard return

Thanks Tom and Doc for the suggestions

Finally I found out it works when I use:

data want ;

  set have;

  array _char _character_ ;

  do over _char;

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

  end;

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 2,113

Re: sas dataset exported to csv file: lay-out corrupt because of hard return

Check out the documentation for the COMPRESS function.  You can use it to strip the <EOL> string from your data.

The Windows string that you want to remove is actually two characters <CR> and <LF> and they are represented as '09'X and '0A'X in hex, so the function would look something like

short_descr=COMPRESS(short_descr,'090A'x);

(The <EOL> string is different if the source data are in Unix.)

Doc Muhlbaier

Duke

Super User
Super User
Posts: 6,502

Re: sas dataset exported to csv file: lay-out corrupt because of hard return

Looks like it has a carriage return ('0D'x) embedded in the string.  This normally is caused by reading a text file created on Windows/DOS where the end of line is marked by the carriage return line feed pair ('0D0A'x) on a Unix system where end of line is marked only by the line feed ('0A'x). So the CR inserted by DOS is read as the last character of the line.

It is probably easier to just remove the CR's from the file.  Here is a generic data step to remove them from all character variables.

data want ;

  set have;

  array _char _character_ ;

  do over _char;

    _char=compress(_char,'0D'x);

  end;

run;

Trusted Advisor
Posts: 2,113

Re: sas dataset exported to csv file: lay-out corrupt because of hard return

Tom,

Thanks for the update on the <EOL> hex code.  Rusty brain cells today...

Doc

Solution
‎09-19-2012 03:42 AM
Occasional Contributor
Posts: 12

Re: sas dataset exported to csv file: lay-out corrupt because of hard return

Thanks Tom and Doc for the suggestions

Finally I found out it works when I use:

data want ;

  set have;

  array _char _character_ ;

  do over _char;

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

  end;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 2044 views
  • 7 likes
  • 3 in conversation