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

    Posted on June 25, 2012 by in Macros, SAS

    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";
    Be Sociable, Share!
      Post Tagged with

    Written by

    View all articles by

    Email : [email protected]

    Leave a Reply