topic Re: Replace Alt+Enter from Excel in SAS Programming

Replace Alt+Enter from Excel

Reeza — Tue, 23 Apr 2013 17:45:35 GMT

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

Re: Replace Alt+Enter from Excel

Tom — Tue, 23 Apr 2013 18:08:41 GMT

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;

Re: Replace Alt+Enter from Excel

esjackso — Tue, 23 Apr 2013 18:15:13 GMT

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

Re: Replace Alt+Enter from Excel

Reeza — Tue, 23 Apr 2013 19:01:44 GMT

Thanks Tom.

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

Re: Replace Alt+Enter from Excel

esjackso — Tue, 23 Apr 2013 19:56:08 GMT

Gotcha -- didnt think of that.

Re: Replace Alt+Enter from Excel

snoopy369 — Tue, 23 Apr 2013 20:46:18 GMT

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.