ds2text.sas

%macro ds2text
(
  data=&SYSLAST
, dataform=FORMATTED
, path=
, file=
, fileext=tab
, fileref=
, filemode=
, heading=VARLABEL
, delim='09'x
, lineterm=LF
);

  %*
  %* data     - name of data set to export, can include data set options
  %* dataform - FORMATTED or UNFORMATTED.
  %* path     - path to where file will be written
  %* file     - file to be written, default is same name as datas
  %* fileext  - file extension to use when neither file nor fileref provided
  %* fileref  - pre-assigned fileref, takes precendence over path and file
  %* filemode - blank or MOD (for appending)
  %* heading  - VARNAME, VARLABEL or NONE
  %* delim    - character or string to place between column values
  %* lineterm - CRLF, LF or CR
  %*;

  %* Richard A. DeVenezia
  %*
  %* Note: all data output is
  %*
  %* mod
  %*  9/15/99 rad add optional parameter path
  %* 10/25/99 rad add section to output DATETIME formatted variables
  %*              in format DDMONYY HH:MM:SS
  %*              (the default DDMONYY:HH:MM:SS is not auto-sensed as a
  %*              date value when opening into Excel)
  %*  4/26/00 rad translate cr and lf in character values to vertical bar
  %*              translate delimiters within character values to tilde
  %*  2/19/01 rad fileref EXPORT changed to a random number reference
  %*              this will prevent filename clashes
  %*  7/ 1/01 rad when heading=labels is requested, use names when labels
  %*              are blank.  Also, double quote heading values.
  %* 12/08/93 rad update to use attrX() functions and
  %*              more reasonable parameter names
  %*;

  %local version;
  %let version = 010701;

  %*-----;

  %let dataform = %upcase(&dataform);

  %if &dataform ne FORMATTED and
      &dataform ne UNFORMATTED
  %then %do;
    %put ERROR: dataform &dataform is invalid.;
    %goto EndMacro;
  %end;

  %*-----;

  %let heading = %upcase(&heading);

  %if &heading ne VARNAME and
      &heading ne VARLABEL and
      &heading ne NONE
  %then %do;
    %put ERROR: heading &heading is invalid.;
    %goto EndMacro;
  %end;

  %*-----;

  %let lineterm = %upcase(&lineterm);

  %if &lineterm ne CRLF and
      &lineterm ne CR and
      &lineterm ne LF
  %then %do;
    %put ERROR: lineterm &lineterm is invalid.;
    %goto EndMacro;
  %end;


  %if &lineterm = CRLF %then
    %let lineterm = '0d0a'x;
  %else
  %if &lineterm = CR %then
    %let lineterm = '0d'x;
  %else
    %let lineterm = '0a'x;

  %*-----;

  %if %length (%nrbquote(&delim))=1 %then
    %let delim=%str(%')%nrbquote(&delim)%str(%');

  %*-----;

  %local random;
  %let random = _%substr(%sysfunc(ranuni(0),9.7),3);

  %*-----;

  proc contents noprint
       data=&data
       out=&random(keep=libname memname name varnum label format);
  proc sort data=&random; by varnum;
  run;

  %local dsid i nvars;

  %let dsid = %sysfunc (open(&random));
  %if &dsid < 1 %then %do;
    %put ERROR: Could not open &random;
    %goto EndMacro;
  %end;

  %local libname memname name varnum label format;

  %syscall set(dsid);

  %let i=0;
  %do %while (0=%sysfunc(fetch(&dsid)));
    %let i = %eval(&i+1);

    %local varname&i varnum&i varlabel&i varfmt&i;

    %let varname&i  = %qtrim(%superq(name));
    %let varnum&i   = &varnum;
    %let varlabel&i = %qtrim(%superq(label));
    %let varfmt&i   = &format;

    %put &i &&varname&i &&varnum&i &&varlabel&i &&varfmt&i;
  %end;

  %let nvars=&i;

  %let dsid = %sysfunc (close(&dsid));

  proc sql;
    drop table &random;
  quit;

  %*---------------------------------------------------------------------------;
  %* output data to text file;

  data _null_ ;

  %if %length(&fileref) %then
    file &fileref
  ;
  %else
  %if %length(&path) and %length(&file) %then
    file "&path./&file."
  ;
  %else
  %if %length(&path) %then
    file "&path./&libname..&memname..&fileext."
  ;
  %else
  %if %length(&file) %then
    file "./&file"
  ;
  %else
    file "./&libname..&memname..&fileext."
  ;

      recfm=N lrecl=10000 &filemode;

%if &heading ne NONE %then %do;

    %local heading dlm;

    put
    %do i = 1 %to &nvars;
      %let header = &&&HEADING.&I;
      %if %length(&header)=0 %then %let header=&&varname&i;
      &dlm %unquote(%str(%')%nrquote(&header)%str(%'))
      %let dlm=&delim;
    %end;
    &lineterm
    ;

%end;

    do while (not &random);

      set &data end=&random;

     %* Excel does not like : between date and time, so take it out;

      %do i = 1 %to &nvars;
      %if &&varfmt&i = DATETIME and &dataform = FORMATTED %then %do;

        __d&i = datepart(&&varname&i);
        __t&i = timepart(&&varname&i);

      %end;
      %end;

      put
      %let dlm=;
      %do i = 1 %to &nvars;
        &dlm

        %if &&varfmt&i = DATETIME and &dataform = FORMATTED %then %do;

          __d&i date9. __t&i time8.

        %end;
        %else
          &&varname&i
        ;

        %let dlm=&delim;
      %end;
      &lineterm
      ;

    end;

%if &dataform = UNFORMATTED %then %do;

    format
    %do i = 1 %to &nvars;
      &&varname&i
    %end;
    ;

%end;

    stop;
  run;

%EndMacro:

%mend ds2text;

Sample code

/*
options nosource;

proc format;
  value $yesno 'Yes'='Y' 'No'='N';
  value  yesno 1='Yes' 0='No';
run;

data test;
  a=123.159;
  b='Yes';
  c=1;
  b2=b;
  c2=c;
  d=constant('pi');
  dt=datetime();d_=date();
  t_=time();
  format a 6.2;
  format b $yesno.;
  format c yesno.;
  format dt datetime22.;
  format d_ mmddyy10.;
  format t_ time8.;
  label d_='Date' t_='Time' d='Pi' b='Yes/No' c='0/1' dt='Date&Time';
run;

options mprint;

filename doh "%sysfunc(pathname(WORK))/doh.xls";
%ds2text
( data=test(where=(c=1) keep=a b c d dt d_ t_)
, fileref=doh
);

x "%sysfunc(pathname(DOH))";

options source;
*/