BookmarkSubscribeRSS Feed
louisehadden
Quartz | Level 8

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

9 REPLIES 9
Tom
Super User Tom
Super User

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?

louisehadden
Quartz | Level 8

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/>.

Tom
Super User Tom
Super User

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;

louisehadden
Quartz | Level 8

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!

Tom
Super User Tom
Super User

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)

louisehadden
Quartz | Level 8

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...

data_null__
Jade | Level 19

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;
louisehadden
Quartz | Level 8

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2295 views
  • 0 likes
  • 4 in conversation