Posted By

sastechies on 01/26/10


Tagged

file text macro delimited sas


Versions (?)

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


 / Published in: SAS
 

URL: http://sastechies.blogspot.com/2009/11/sas-macro-to-create-delimited-text-file.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");

Report this snippet  

You need to login to post a comment.