DATA Step, Macro, Functions and more

Removing empty line breaks

Reply
Contributor
Posts: 39

Removing empty line breaks

[ Edited ]

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

Regular Contributor
Posts: 191

Re: Removing empty line breaks

Posted in reply to camfarrell25

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

//Fredrik

Contributor
Posts: 39

Re: Removing empty line breaks

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

Frequent Contributor
Posts: 129

Re: Removing empty line breaks

[ Edited ]
Posted in reply to camfarrell25

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 -

Super User
Super User
Posts: 7,080

Re: Removing empty line breaks

Posted in reply to camfarrell25

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;
Ask a Question
Discussion stats
  • 4 replies
  • 125 views
  • 0 likes
  • 4 in conversation