08-29-2017 10:59 PM - edited 08-29-2017 10:59 PM
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...
08-30-2017 09:12 AM
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.
What Compress(text, ,"kw") does:
Line 1 Line 2
what would be ideal
08-30-2017 06:08 AM - edited 08-30-2017 06:17 AM
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 -
08-30-2017 12:46 PM
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;