topic Re: Removing empty line breaks in SAS Programming

Removing empty line breaks

camfarrell25 — Wed, 30 Aug 2017 02:59:33 GMT

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!

Re: Removing empty line breaks

FredrikE — Wed, 30 Aug 2017 09:29:02 GMT

Have you tried compress with 'kw' (keep writable) ?

//Fredrik

Re: Removing empty line breaks

Oligolas — Wed, 30 Aug 2017 10:17:31 GMT

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;

Re: Removing empty line breaks

camfarrell25 — Wed, 30 Aug 2017 13:12:58 GMT

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

Re: Removing empty line breaks

Tom — Wed, 30 Aug 2017 16:46:41 GMT

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;