topic Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character <BR/> in SAS Programming

How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

louisehadden — Mon, 06 May 2013 18:59:09 GMT

We have a series of files that we need to export to tab delimited files each month. The process is seamless using PROC EXPORT except for this one file that has the giant variable in it. Because there are embedded tabs (non-printing) in the variable, (plus its length exceeds the LRECL allowed in PROC EXPORT), exported to a tab delimited file does not work. Because this field will be displayed on a web site, we need to retain the functionality of actual line breaks, while removing the tabs so we can transfer the data. So far, no luck with TRANWRD or COMPRESS - although you can use COMPRESS to remove ALL "space" characters, that also removes the blanks between works and doesn't give us the line breaks. Any thoughts? TIA.

Louise

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

Tom — Mon, 06 May 2013 19:04:28 GMT

Not sure what you want, but you can easily generate TAB delimited file using a DATA step.

For example if you do not need header line it is a simple as:

data _null_;

set have ;

file .... dlm='09'x dsd ;

input (_all_) (:) ;

run;

What do you want to happen differently?

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

louisehadden — Mon, 06 May 2013 19:06:57 GMT

I have this part working fine using a data step. What I can't do is "fix" the long variable by replacing the embedded tabs with a space and replacing the embedded line feeds (both non printing characters) with an html line break <BR/>.

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

Tom — Mon, 06 May 2013 19:17:52 GMT

To replace the a tab with a space you translate (or tranwrd) and you will not have any trouble as there will be no change in the length of the string. VAR=TRANSLATE(VAR,' ','09'x) ;

To generate the <BR/> on the output to replace the linefeed you will could have issues using TRANWRD() function because the result could be longer than the maximum length of a string. One way to prevent this is to process the variable character by character.

do _n_=1 to length(var) ;

_char_=substr(var,_n_,1);

if _char_='0A'x then put '<BR/>' @;

else put _char_ $char1. @;

end;

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

louisehadden — Mon, 06 May 2013 20:16:03 GMT

Thanks, this is giving me a partial solution! I still have problems with double quotes and double line feeds, but the end of the tunnel is in sight!

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

data_null__ — Mon, 06 May 2013 20:24:23 GMT

PROC EXPORT should honor the LRECL of a FILEREF but it does not. If you run this, the generated code has LRECL=32767 on the FILE statement. That sounds like the buzzing a a bug to me.

filename out '~/lrecl.txt' lrecl=47802; 
proc export outfile=out data=sashelp.class dbms=tab;
   run;

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

Tom — Mon, 06 May 2013 20:25:27 GMT

Normally (at least in the SAS world) you would double the double quotes in the string when you are using double quotes around the string. So you could add

else if _char_='"' then put '""' @ ;

before the ELSE clause to handle that.

Not sure what multiple LF is about, but you could fix that with TRANWRD() as it will only make the value shorter. But it would miss when there is space or other characters between them.

VAR = TRANWRD(VAR,'0A0A'x,'0A'x)

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

louisehadden — Mon, 06 May 2013 22:50:33 GMT

I submitted this issue to tech support but it was pretty clear they both knew about it and weren't going to change it. That issue is solved by using a data step which DOES support the longer file length. My problem is the delimited requested (tabs) and the fact that the delimiter is embedded in that one long text field!

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

louisehadden — Mon, 06 May 2013 22:52:16 GMT

The field is created in an entry screen in some unknown (to me) software - there are definitely non-printing line feeds, tabs, and who knows what else in the field along with the double quotes. We redact all PII from the text field before it gets put out using Perl regular expressions. There may be a solution there...

Re: How to convert tabs, line feeds, etc. in a long text field (32767 bytes) to html line feed character

Ksharp — Tue, 07 May 2013 01:59:45 GMT

Or consider using Perl Regular Expression.

prxchange()