24727 - Robust macro to write a delimited file

Sample 24727: Robust macro to write a delimited file

options mprint;

data one;
  input id name :$20. amount ;
  date=today();
  format amount dollar10.2
           date mmddyy10.;
  label id="Customer ID Number";
datalines;
1 Grant   57.23
2 Michael 45.68
3 Tammy   53.21
;


%macro makefile
  (
   dataset=_last_ ,  /* Dataset to write */
   filename=print ,  /* File to write to */
   dlmr=","       ,  /* Delimiter between values */
   qtes="no"      ,  /* Should SAS quote all character variables? */
   header="no"    ,  /* Do you want a header line w/ column names? */
   label="no"        /* Should labels be used instead of var names in header? */
  );

proc contents data=&dataset out=___out_;
run;

/* Return to orig order */

proc sort data=___out_; 
  by varnum;       
run;

/* Build list of variable names */

data _null_;                           
  set ___out_ nobs=count;
  call symput("name"!!left(_n_),trim(left(name)));
  call symput("type"!!left(_n_),trim(left(type)));

  /* Use var name when label not present */
  if label=" " then label=name;         
  call symput("lbl"!!left(_n_),trim(left(label)));
  if _n_=1 then call symput("numvars", trim(left(put(count, best.))));
run;

/* Create file */

data _null_;
  set &dataset;
  file &filename;
  %global temp;
  %if &qtes="yes" %then %let temp='"';
  %else %let temp=' ';
  %if &header="yes" %then %do;

    /* Conditionally add column names */
    if _n_=1 then do;    
        put %if &label="yes" %then %do;
        %do i=1 %to &numvars-1;
          &temp  "%trim(%bquote(&&lbl&i)) " +(-1) &temp &dlmr
          %end;
        &temp "%trim(%bquote(&&lbl&numvars)) " &temp;
        %end;
    %else %do;
      %do i=1 %to &numvars-1;
        &temp "%trim(&&name&i) " +(-1) &temp &dlmr
        %end;
       &temp "%trim(&&name&numvars) " &temp ;
       %end;
    ;
    end;
  %end;

  /* Build PUT stmt to write values */
  put                                   
     %do i = 1 %to &numvars -1;
       %if &&type&i ne 1 and &qtes="yes" %then %do;
         '"' &&name&i +(-1) '"' &dlmr
         %end;
       %else %do;
         &&name&i +(-1) &dlmr
         %end;
     %end;
     %if &&type&i ne 1 and &qtes="yes" %then %do;

       /* Write last varname */
       '"' &&name&numvars +(-1) '"';     
       %end;
       %else %do;

         /* Write last varname */
         &&name&numvars;                   
       %end;
  run;
%mend makefile;

/* If LRECL= required because of records longer the 256, specify here */

filename myfile "~/tmp/rawdata" lrecl=1000;


/* Invoke macro to write to a file, include proper parameters for your case. */
/* Make sure that the variables are in the order you want and have the       */
/* desired formats.                                                          */

%makefile(dataset=one,
          filename=print,     /* FILEREF or DDNAME of the file */
          dlmr=",",
          qtes="yes",
          header="yes",
          label="yes");
run;

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

"Customer ID Number","name","amount","date "
1,"Grant",$57.23,07/17/2003
2,"Michael",$45.68,07/17/2003
3,"Tammy",$53.21,07/17/2003

Type:	Sample
Topic:	SAS Reference ==> DATA Step Common Programming Tasks ==> Reading and Writing External Data SAS Reference ==> Macro

Date Modified:	2009-12-24 09:07:45
Date Created:	2004-09-30 14:09:08

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	All	n/a	n/a

Support

Sample 24727: Robust macro to write a delimited file

Operating System and Release Information