safe_ds2csv.sas

%macro safe_ds2csv
/*----------------------------------------------------------------------
Write SAS dataset as CSV file insuring proper quoting
----------------------------------------------------------------------*/
(dsn         /* Input dataset name. DSNoptions allowed */
,outfile=csv /* Output fileref or quoted filenam. Can include options */
             /* valid on a  FILE statement except the options */
             /* DSD, DLM= and TERMSTR= generated by the macro. */
,dlm=','  /* Delimiter character as string literal */
,names=1  /* Write header row? (0/1)  */
,label=0  /* Use LABEL instead of NAME for header row? (0/1) */
,format=  /* Optional format overrides. */
,maxchar=300 /* Optional max length for any formatted value max=32767 */
);
/*----------------------------------------------------------------------
Write dataset to a delimited file that SAS should be able to read back.

All lines will end with the Windows/DOS standard CR+LF sequence so that
SAS can read values that have embedded CR or LF characters. Any CR+LF
two byte sequences in the data will be written as just a CR.

Also to prevent SAS from getting confused by single quote values in two
different fields on the same line from looking like one long quoted
string any value with a single quote will be quoted in the output file.

Uses the CALL VNEXT() idea originally posted online by data_null_ in
many places.  For example look at this thread on SAS Communities
https://communities.sas.com/t5/Base-SAS-Programming/Output-to-delimited-format/m-p/292767#M60829

To avoid any name conflicts uses temporary arrays named _CHAR_ and
_CHARACTER_ which SAS allows as array names but not as variable names.

Notes:
- To pass a physical name for a file enclose it in quotes.
- To pass a different delimiter use a string literal.
    - You can use hex literal for delimiter. '09'x is a TAB character.
- To suppress header row use NAMES=0.
    - To use LABEL instead of NAME in header row use LABEL=1.
- Do not include the FORMAT keyword in the FORMAT= option.
- To prevent line wrapping with datasets that have long values or a
  large number of variables include LRECL= option in OUTFILE.
- Use the TERMSTR=CRLF option when reading the resulting file to make
  sure that embedded CR or LF characters will not cause the input to
  get confused.
- Set MAXCHAR long enough for the largest value of any variable.  Note
  that you will get improved performance by using a smaller value for MAXCHAR
  since the perfomance of string operations is much worse for long
  strings.

Examples:
* Create CSV file ;
  %safe_ds2csv(mydataset,outfile='myfile.csv' lrecl=50000)

* Read generated CSV using TERMSTR=CRLF option;
  data mydataset;
    infile 'myfile.csv' dsd lrecl=50000 truncover firstobs=2 termstr=crlf ;
    input .... ;
  run;

----------------------------------------------------------------------*/
%local optsave;
%let optsave="%qsysfunc(getoption(missing))";
options missing=' ';
*----------------------------------------------------------------------------;
* Write data values to delimited text file using PUT statements. ;
*----------------------------------------------------------------------------;
data _null_;
  set &dsn;
  format &format;
  array _char_(1) $32 _temporary_;
  array _character_(1) $&maxchar. _temporary_;
  file &outfile dsd dlm=&dlm
%if %qupcase(&outfile) ne LOG %then termstr=crlf;
  ;
%if (&names) %then %do;
*----------------------------------------------------------------------------;
* Write the header row ;
*----------------------------------------------------------------------------;
  if _n_ eq 1 then do;
    do while(1);
      call vnext(_char_(1));
      if _char_(1) = '_ERROR_' then leave;
      _character_(1) =
  %if (&label) %then vlabelx(_char_(1));
  %else _char_(1);
      ;
      link write;
    end;
    put;
  end;
%end;
  _char_(1)=' ';
  do while(1);
    call vnext(_char_(1));
    if _char_(1) = '_ERROR_' then leave;
    _character_(1) = vvaluex(_char_(1));
    link write;
  end;
  put;
return;
*----------------------------------------------------------------------------;
* Write one value. Force quotes when it contains single quote, CR or LF ;
* Replace any CRLF pair with CR only ;
*----------------------------------------------------------------------------;
write:
  if indexc(_character_(1),"'",'0D0A'x) then do;
     _character_(1)= tranwrd(_character_(1),'0D0A'x,'0D'x);
     put _character_(1) ~ @;
  end;
  else put _character_(1) @;
return;
run;
options missing=&optsave;
%mend safe_ds2csv;