Choose delimiter in proc export

[Ronein]

Hello
I have a very big data set of 150 million rows with 5 char columns and 15 numeric.I want to check which special characters ( characters that are not numbers and not letters) are in the char columns. It will help me to choose the delimiter when i export the data set into txt file. I must choose delimiter that is not existing as special character in char columns

[Tom]

Why do you think it matters what delimiter you use?  SAS will create a valid CSV file with any delimiter.  If any value includes the delimiter (or a quote) then the value will be quoted.

Sounds like your real problem is that the consumer of the file does not know how to read a CSV file.

If you really must try to find a character that never appears in your data then you will have make two passes thru the data.  One to check for a possible delimiter 

data _null_;
   set have end=eof;
   array _c _character_;
   length _x $128;
   retain _x ;
   if _n_=1 then do;
      _x=collate(0,128);
      _x=compress(_x,cat(' ','00'x,'FF'x,'"'),'DF');
  end;
  do over _c;
    _x = compress(_x,_c);
   end;
   if eof then call symputx('dlm',put(_x,$hex2.));
run;

and a second to actually write the CSV file.

proc export data=have file="want.csv" dbms=csv;
   delimiter="&dlm"x;
run;

[Ronein]

Thanks,
If I use delimiter that appears in the char columns then when the user of the txt file will import it into sas then SAS can mix values from different columns.
This is my opinion, what do you think?

[Kurt_Bremser]

---

@Ronein wrote:
Thanks,
If I use delimiter that appears in the char columns then when the user of the txt file will import it into sas then SAS can mix values from different columns.
This is my opinion, what do you think?

---

No. By using the DSD option, columns containing the delimiter will be quoted. The only real "troublemakers" are LF or CRLF, which should be eliminated from the data anyway.

[Ronein]

Thanks,

May you please show the code where I need to add  DSD option?

Do you mean that I need to run "Way2" and not "Way1"?

Which way is better- way2 or way1?

/********Way1***************/
/********Way1***************/
/********Way1***************/
libname LS '/usr/local/SAS/SASUsers/LabRet/Adhoc/creditCards/';
proc export data=LS.lightSolver_sofi(obs=10) 
outfile='/usr/local/SAS/SASUsers/LabRet/Adhoc/creditCards/lightSolver_sofi.txt'
dbms = dlm  replace;
delimiter = '|';
run;

/********Way2***************/
/********Way2***************/
/********Way2***************/
libname LS '/usr/local/SAS/SASUsers/LabRet/Adhoc/creditCards/';
%macro export_flat_files;
%let file_extract='/usr/local/SAS/SASUsers/LabRet/Adhoc/creditCards/lightSolver_sofi2.txt';
%put &file_extract;
data _null_;
file &file_extract dsd dlm = '|';
set LS.lightSolver_sofi(obs=10) ;
put (_all_) (~);
run;
%mend;
%export_flat_files;

[Kurt_Bremser]

Read the log to see the code generated by PROC EXPORT. You may already see the option used there.

If you write the DATA step yourself, don't forget a LRECL= option with a sufficient size for the output buffer.