05-06-2013 02:59 PM
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.
05-06-2013 03:04 PM
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:
set have ;
file .... dlm='09'x dsd ;
input (_all_) ( ;
What do you want to happen differently?
05-06-2013 03:06 PM
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/>.
05-06-2013 03:17 PM
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) ;
if _char_='0A'x then put '<BR/>' @;
else put _char_ $char1. @;
05-06-2013 04:16 PM
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!
05-06-2013 04:25 PM
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)
05-06-2013 06:52 PM
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...
05-06-2013 04:24 PM
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.
05-06-2013 06:50 PM
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!