- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
I'm curently trying to remove some empty line breaks (as shown in the attachments) in order to export to excel and it'll look like that.
I'm then transposing a collection of these strings variables and concatenating them before exporting to excel.
I've tried a few options compress with '0A'x, with '0D'x, with ' ', trim but with no succes...
Thanks!
CF
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Have you tried compress with 'kw' (keep writable) ?
//Fredrik
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
That worked quite well actually but I'm having an issue where two "blocks" of text are now back to back and I would like to be able to seperate them either with a "-" or just remove the space that separates two blocks of text.
Line 1
Line 2
What Compress(text, ,"kw") does:
Line 1 Line 2
what would be ideal
Line 1
Line2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
As @FredrikE mentions, the kw compress option is an excellent way to remove the non-printable characters (first 32 sign in the ASCII symbol table)
You might as well want to remove these symbols in the labels too:
**Delete the chars; DATA want; SET sheet1; array allChars _CHARACTER_; do over allChars; allChars=compress(allChars,,'kw'); end; RUN; **Replace not printable characters in labels too; DATA _NULL_; set sashelp.vcolumn end=eof; where libname eq "WORK" and memname eq "WANT" and not missing(label); label=compress(label,,'kw'); if _N_ eq 1 then call execute('PROC DATASETS lib='||strip(libname)||' nolist; modify '||strip(memname)||';'); call execute('label '||strip(name)||'= "'||strip(label)||'";'); if eof then call execute('RUN; QUIT;'); RUN;
Yet sometimes this might result in a string concatenation where a line break is separating 2 words.
In this case you need a work-around
-to replace the line breaks by a space
-remove duplicate blanks
-remove leading/trailing blanks
DATA want2; SET sheet1; length byteSearch $32; array allChars _CHARACTER_; do byteI=1 to 31; byteSearch=strip(byteSearch)||byte(byteI); end; do over allChars; allChars=strip(compbl(translate(allChars,repeat(' ',33),byteSearch))); end; drop byte:; RUN;
- Cheers -
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Are you sure that you have any line breaks in the data? The file you showed looked to have and empty cell instead. So if your OLD dataset has a single variable named HAVE you can make a new dataset name NEW that has the empty rows removed.
data new ;
set old;
if missing(have) then delete;
run;