Exporting to Text using 2-character delimiters

[RandoDando]

Is there a way to use PROC Export to export a data set to a delimited text file using a custom 2 character delimiter?  My data includes a text field with long entries made by users, and frankly I am afraid that one day someone will have a delimiter in their text and throw off everything.  It's happened before.  I am using pipe (|) now, and it works for the time being.  But, in the long run I'd prefer to use something like |# as the delimiter.  I read online that Export does not support DLMSTR which is what this would require, as only the first row has double delimiters when I try using DBMS=DLM.   Is there another programmatic way around this?  I'd prefer not to use the File menu.

Hopefully, a future update will add dbms=DLMSTR to Proc Export.

[PeterClemmensen]

How about using the File Statement in the Data step? The File Statement accepts a 'list-of-delimiting-characters' as delimiter on the Delimiter Option.

[RandoDando]

Yes, I've only used that for importing. I can understand how it works for exporting to Workbooks, but I am going to have to really dig into the weeds to see how to use it for exporting to text.

[PeterClemmensen · Accepted solution]

Try something like

data _null_;
   set sashelp.class;
   file 'yourpath/class.txt' dlmstr='|#';
   put name age sex;
run;

[RandoDando]

That works. I just needed to add the replace option after the delimiter. like:

data _null_;
   set sashelp.class;
   file 'yourpath/class.txt' dlmstr='|#'; replace;
   put name age sex;
run;

[RandoDando]

As well as add the column headings.

data _null_;
   set sashelp.class;
   file 'yourpath/class.txt' dlmstr='|#'; replace;
if _n_ = 1 then do;
   put "name" '|#' "age" '|#' "sex";
end;
if _n_ > 1 then do;
put name age sex;
end;
run;

[Tom]

Are the users TYPING the text files?  If not then it should not matter if the text contains the delimiter character because a properly created delimited text file will have quotes added around values that contain the delimiter character (or the quote character).  Find out what tool they are using to make the file and make sure they use it in a way that make files that can be parsed.

Note that there is no need to use PROC EXPORT to make a delimited text file from a dataset.  All it will do is write a data step for you.  So just write the data step yourself.  Then you can use the DLMSTR= option on the FILE statement if you did want to insert some goofy multiple character delimiter.

data _null_;
  set sashelp.class(obs=3);
  file txt dlmstr='||';
  put (_all_) (+0) ;
run;

If you need to make a header row then do that first and then append the data lines.

proc transpose data=sashelp.class(obs=0) out=names;
  var _all_;
run;

data _null_;
  set names ;
  file txt dlmstr='||';
  put _name_ @ ;
run;

data _null_;
  set sashelp.class(obs=3);
  file txt mod dlmstr='||';
  put (_all_) (+0) ;
run;

Result

Name||Sex||Age||Height||Weight
Alfred||M||14||69||112.5
Alice||F||13||56.5||84
Barbara||F||13||65.3||98

[RandoDando]

Thanks. I will try both solutions here and see. The open text field I have in my data is ...TOO open (I'm not the one who set up the system which collects and stores this data, so it's out of my control). It does include some quotes in a few cases, which would throw off my data if I tried to enclose the text with quotes, been there done that. That's why I am resorting to goofy double delimiters.

[Tom]

The main thing that can mess up using delimited text files with SAS are embedded end of line markers.  You can fix that by making sure that you use CRLF as the end of line marker and remove/replace any CRLF pairs from the text fields.

data clean ;
  set have;
  array _char_ _character_;
  do over _char_;
    _char_=tranwrd(_char_,'0D0A'x,'0D'x);
  end;
run;

Now you can test if the resulting file can be written out and re-read with something like this:

filename csv temp;
data _null_;
  set clearn;
  file csv dsd termstr=crlf lrecl=1000000;
  put (_all_) (+0);
run;
data test;
  infile csv dsd termstr=crlf truncover lrecl=1000000;
  if 0 then set clean;
  input (_all_) (+0);
run;
proc compare data=test compare=clean;
run;

The only other rare thing that will confuse SAS reading of delimited files is the presence of two fields in the same observations that have the earlier one starting with single quote and the later one ending with single quote. This can cause a line that looks like:

val1,'val2,val3,val4',val5

Which SAS will read as three values instead of five when trying to read it because it will treat the single quotes around a value the same as it does double quotes around a value.