DATA Step, Macro, Functions and more

Replace Alt+Enter from Excel

Accepted Solution Solved
Reply
Super User
Posts: 19,771
Accepted Solution

Replace Alt+Enter from Excel

I've imported an Excel file, which appears to have imported correctly.

However fields that have the Alt+Enter have a box [] in them, and I'd like to replace it with a space instead.

The compress function with "CH" parameters removes them, but I need a space instead.

The solutions I've found indicate saving as CSV instead, but I'd like to modify the data rather than re-import.

I thought about the translate and tranwrd function, but couldn't figure out how to specify my space. I think its "A0"x but not sure how to verify that either.

TIA,

Reeza


Accepted Solutions
Solution
‎04-23-2013 02:08 PM
Super User
Super User
Posts: 7,039

Re: Replace Alt+Enter from Excel

Most likely the character is '0A'x, which is a linefeed.

data want ;

   set have ;

   array _c _character_ ;

   do over _c;

    _c = translate(_c,' ','0A'x) ;

  end;

run;

View solution in original post


All Replies
Solution
‎04-23-2013 02:08 PM
Super User
Super User
Posts: 7,039

Re: Replace Alt+Enter from Excel

Most likely the character is '0A'x, which is a linefeed.

data want ;

   set have ;

   array _c _character_ ;

   do over _c;

    _c = translate(_c,' ','0A'x) ;

  end;

run;

Super Contributor
Posts: 334

Re: Replace Alt+Enter from Excel

Would compressing out control charters work? compress(var,'','C')?

EJ

Super User
Posts: 19,771

Re: Replace Alt+Enter from Excel

Thanks Tom.

Eric, that removes it, but I need a space in that location, otherwise my cities (Red[]Deer) becomes RedDeer.

Super Contributor
Posts: 334

Re: Replace Alt+Enter from Excel

Gotcha -- didnt think of that.

EJ

Super Contributor
Posts: 253

Re: Replace Alt+Enter from Excel

Would be nice if COMPRESS had an option to replace-with-space instead of remove - of course that's what TRANSLATE does, but writing it as

x=compress(x,,'cz'); *where z=replace with space;

would be easier than the translate code when dealing with longer lists of things.

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 976 views
  • 0 likes
  • 4 in conversation