Replace Alt+Enter from Excel

[Reeza]

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

[Tom · Accepted solution]

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;

[esjackso]

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

EJ

[Reeza]

Thanks Tom.

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

[esjackso]

Gotcha -- didnt think of that.

EJ

[snoopy369]

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.