Nov 16, 2009

SAS Macro to Create a delimited file from a SAS data set..

SAS has a macro that creates a delimited text file from a SAS dataset @ SAS Macro to Create a delimited text file from a SAS data set..
%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_;
/* Return to orig order */
proc sort data=___out_;
  by varnum;     
/* Build list of variable names */
data _null_;                         
  set ___out_ nobs=count;
  call symput("name"!!left(put(_n_,3.)),name);
  call symput("type"!!left(put(_n_,3.)),type);
  /* Use var name when label not present */
  if label=" " then label=name;       
  call symput("lbl"!!left(put(_n_,3.)),label);
  if _n_=1 then call symput("numvars", trim(left(put(count, best.))));
/* 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
        &temp "%trim(%bquote(&&lbl&numvars)) " &temp;
    %else %do;
      %do i=1 %to &numvars-1;
        &temp "%trim(&&name&i) " +(-1) &temp &dlmr
       &temp "%trim(&&name&numvars) " &temp ;
  /* Build PUT stmt to write values */
     %do i = 1 %to &numvars -1;
       %if &&type&i ne 1 and &qtes="yes" %then %do;
         '"' &&name&i +(-1) '"' &dlmr
       %else %do;
         &&name&i +(-1) &dlmr
     %if &&type&i ne 1 and &qtes="yes" %then %do;
       /* Write last varname */
       '"' &&name&numvars +(-1) '"';   
       %else %do;
         /* Write last varname */
%mend makefile;
/* Write last varname */
options mprint;
data one;
  input id name :$20. amount ;
  format amount dollar10.2
           date mmddyy10.;
  label id="Customer ID Number";
1 Grant   57.23
2 Michael 45.68
3 Tammy   53.21
/* If LRECL= required because of records longer the 256, specify here */
filename myfile "~/tmp/rawdata" lrecl=256;
/* 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.                                                          */
          filename=myfile, /* FILEREF or DDNAME of the file */
There is one another simple method below but does not give that many options as the macro above...
%macro delimitfile(dsn,dlm,fileref);
     data _null_;
     file &fileref dlm=&dlm;
     set &dsn;
     put (_all_) (:); /* The colon here is dummy and has no effect */
%mend delimitfile;
filename out "c:\out1.csv";

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.