Advanced Search

SAS Macro to create a delimited text /csv file from a SAS data set..

/ Published in: SAS
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. /*Lets take an example*/
  2.  
  3. options source mprint;
  4.  
  5. data one;
  6.   input id name :$20. amount ;
  7.   date=today();
  8.   format amount dollar10.2 date mmddyy10.;
  9.   label id="Customer ID Number";
  10. datalines;
  11. 1 Grant   57.23
  12. 2 Michael 45.68
  13. 3 Tammy   53.21
  14. ;
  15. run;
  16.  
  17. %macro makefile
  18.   (
  19.    dataset=_last_ ,  /* Dataset to write */ 
  20.    filename=print ,  /* File to write to */ 
  21.    dlmr=","       ,  /* Delimiter between values */ 
  22.    qtes="no"      ,  /* Should SAS quote all character variables? */ 
  23.    header="no"    ,  /* Do you want a header line w/ column names? */ 
  24.    label="no"        /* Should labels be used instead of var names in header? */ 
  25.   );
  26.  
  27.  
  28. proc contents data=&dataset out=___out_;run;
  29.  
  30. /* Return to orig order */ 
  31. proc sort data=___out_; 
  32.   by varnum;       
  33. run;
  34.  
  35. /* Build list of variable names */ 
  36. data _null_;                           
  37.   set ___out_ nobs=count;
  38.   call symput("name"!!left(put(_n_,3.)),name);
  39.   call symput("type"!!left(put(_n_,3.)),type);
  40.  
  41.   /* Use var name when label not present */ 
  42.   if label=" " then label=name;         
  43.   call symput("lbl"!!left(put(_n_,3.)),label);
  44.   if _n_=1 then call symput("numvars", trim(left(put(count, best.))));
  45. run;
  46.  
  47. /*Remove the temporary contents dataset created above*/
  48. proc datasets lib=work nolist;
  49.  delete ___out_;
  50. quit;
  51.  
  52. /* Create file */ 
  53.  
  54. data _null_;
  55.   set &dataset;
  56.   file &filename;
  57.   %global temp;
  58.   %if &qtes="yes" %then %let temp='"';
  59.   %else %let temp=' ';
  60.  
  61. %if &header="yes" %then 
  62. %do;
  63.     /* Conditionally add column names */ 
  64.   if _n_=1 then 
  65.   do;    
  66.         put %if &label="yes" %then 
  67.         %do;
  68.            %do i=1 %to &numvars-1;
  69.                &temp  "%trim(%bquote(&&lbl&i)) " +(-1) &temp &dlmr
  70.            %end;
  71.            &temp "%trim(%bquote(&&lbl&numvars)) " &temp;
  72.         %end;
  73.     %else 
  74.     %do;
  75.       %do i=1 %to &numvars-1;
  76.         &temp "%trim(&&name&i) " +(-1) &temp &dlmr
  77.       %end;
  78.       &temp "%trim(&&name&numvars) " &temp ;
  79.     %end;
  80.   end;
  81.  
  82. %end;
  83.  
  84. /* Build PUT stmt to write values */ 
  85.   put                                   
  86.      %do i = 1 %to &numvars -1;
  87.        %if &&type&i ne 1 and &qtes="yes" %then 
  88.          %do;
  89.          '"' &&name&i +(-1) '"' &dlmr
  90.          %end;
  91.  
  92.        %else 
  93.          %do;
  94.             &&name&i +(-1) &dlmr
  95.          %end;
  96.      %end;
  97.  
  98.      %if &&type&i ne 1 and &qtes="yes" %then 
  99.        %do;
  100.            /* Write last varname */ 
  101.             '"' &&name&numvars +(-1) '"';     
  102.        %end;
  103.      %else 
  104.        %do;
  105.          /* Write last varname */ 
  106.          &&name&numvars;                   
  107.        %end;
  108. run;
  109. %mend makefile;
  110.  
  111. /* If LRECL= required because of records longer the 256, specify here */ 
  112. filename mycsv "C:\csvdata.txt" lrecl=1000;
  113. filename mypipe "C:\pipedata.txt" lrecl=1000;
  114. filename mypipe2 "C:\pipedata2.txt" lrecl=1000;
  115.  
  116. /* Invoke macro to write to a file, include proper parameters for your case. */ 
  117. /* Make sure that the variables are in the order you want and have the       */ 
  118. /* desired formats.                                                          */ 
  119.  
  120. %makefile(dataset=one,
  121.           filename=mycsv,     /* FILEREF or DDNAME of the file */ 
  122.           dlmr=",",
  123.           qtes="yes",
  124.           header="yes",
  125.           label="yes");
  126.  
  127. %makefile(dataset=one,
  128.           filename=mypipe,     /* FILEREF or DDNAME of the file */ 
  129.           dlmr="|",
  130.           qtes="yes",
  131.           header="yes",
  132.           label="yes");
  133.  
  134. %makefile(dataset=one,
  135.           filename=mypipe2,     /* FILEREF or DDNAME of the file */ 
  136.           dlmr="|",
  137.           qtes="no",
  138.           header="yes",
  139.           label="yes");

URL: http://sastechies.blogspot.com/2009/11/sas-macro-to-create-delimited-text-file.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.