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. */
);
/*----------------------------------------------------------------------
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 writtne 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

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.

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;

----------------------------------------------------------------------*/
*----------------------------------------------------------------------------;
* Write data values to delimited text file using PUT statements. ;
*----------------------------------------------------------------------------;
data _null_;
  set &dsn;
  format &format;
%* Use an unusual names to minimize the odds of a name conflict ;
  length __0_name_0__ $32 __0_value_0__ $32767;
  file &outfile dsd dlm=&dlm
%if %qupcase(&outfile) ne LOG %then termstr=crlf;
  ;
%if (&names) %then %do;
  if _n_ eq 1 then link names;
%end;
  do while(1);
    call vnext(__0_name_0__);
    if lowcase(__0_name_0__) = '__0_name_0__' then leave;
    __0_value_0__ = vvaluex(__0_name_0__);
    link write;
  end;
  put;
return;
%if (&names) %then %do;
*----------------------------------------------------------------------------;
* Write the header row ;
*----------------------------------------------------------------------------;
names:
  do while(1);
    call vnext(__0_value_0__);
    if lowcase(__0_value_0__) = '__0_name_0__' then leave;
  %if (&label) %then %do;
    __0_value_0__ = vlabelx(__0_value_0__);
  %end;
    link write;
  end;
  put;
return;
%end;
*----------------------------------------------------------------------------;
* Write one value. Force quotes when it contains single quote, CR or LF ;
* Replace any CRLF pair with CR only ;
*----------------------------------------------------------------------------;
write:
  if indexc(__0_value_0__,"'",'0D0A'x) then do;
     __0_value_0__=  tranwrd(__0_value_0__,'0D0A'x,'0D'x);
     put __0_value_0__ ~ @;
  end;
  else put __0_value_0__ @;
return;
run;

%mend safe_ds2csv;