title: Generating Union Statements using Macros in SAS
body: <p>I have a table named mylib.common_noun contains approximately 2000 records, though this can change bi-weekly. I am trying to generate the same number of union statements as the record count in mylib.common_noun</p>
  
  <p>Structure of mylib.common_noun. Two Columns: Keywords and dname</p>
  
  <pre><code>Keywords DNAME
  A        sas1
  B        sas2
  C        sas3
  </code></pre>
  
  <p>For example, say mylib.common_noun contains 3 records, then it should generate the union statements based on the variable dname and then run the following code</p>
  
  <pre><code>proc sql;
  create table mylib.test_union as (select * from mylib.sas1
  union
  select * from chug.sas2
  union
  select * from chug.sas3);quit;
  </code></pre>
  
  <p>I ran the following code based on suggestions but it doesn't work. </p>
  
  <pre><code>filename code temp;
  data _null_;
      file code;
      set mylib.common_noun;
      if _n_&gt;1 then
          put 'create table mylib.test_union as';
      else put 'union '@;
      put 'select * from ' dname;
  
  run;
  proc sql;
      OPTIONS SOURCE2;
  
      %include code;
      ;
  quit;
  </code></pre>
  
  <p>Error in Log:</p>
  
  <pre><code>  40            %include code;
      NOTE: %INCLUDE (level 1) file CODE is file 
      41        +union select * from mylib.sas_out1
                 _____
                 180
      ERROR 180-322: Statement is not valid or it is used out of proper order.
  
      42        +create table mylib.test_union as
      43        +select * from mylib.sas_out2
      44        +create table mylib.test_union as
      45        +select * from mylib.sas_out3
  </code></pre>
  
  <p>Not sure how to make it work</p>