topic Re: Choose delimiter in proc export in SAS Programming

Choose delimiter in proc export

Ronein — Thu, 14 Sep 2023 02:55:28 GMT

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

Re: Choose delimiter in proc export

Tom — Thu, 14 Sep 2023 03:51:50 GMT

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;

Re: Choose delimiter in proc export

Ronein — Thu, 14 Sep 2023 05:26:26 GMT

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?

Re: Choose delimiter in proc export

Kurt_Bremser — Thu, 14 Sep 2023 05:30:13 GMT

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

Re: Choose delimiter in proc export

Ronein — Thu, 14 Sep 2023 06:50:09 GMT

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;

Re: Choose delimiter in proc export

Kurt_Bremser — Thu, 14 Sep 2023 13:41:53 GMT

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.