Generating Union Statements using Macros in SAS

Question

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

Structure of mylib.common_noun. Two Columns: Keywords and dname

Keywords DNAME
A        sas1
B        sas2
C        sas3

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

proc sql;
create table mylib.test_union as (select * from mylib.sas1
union
select * from chug.sas2
union
select * from chug.sas3);quit;

I ran the following code based on suggestions but it doesn't work.

filename code temp;
data _null_;
    file code;
    set mylib.common_noun;
    if _n_>1 then
        put 'create table mylib.test_union as';
    else put 'union '@;
    put 'select * from ' dname;

run;
proc sql;
    OPTIONS SOURCE2;

    %include code;
    ;
quit;

Error in Log:

  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

Not sure how to make it work

Answer 1

Sounds like you need to generate code from data. While you could possibly generate the code using macro logic it might be easier to generate the code just using regular SAS statements instead.

You did not describe the structure of chug.claimants so let's just assume it has one variable named DATASET with values like "chug.sas_claimants2". If not then just add some logic to create such a value from the variables it does have.

filename code temp;
data _null_;
  file code ;
  set chug.claimants ;
  if _n_=1 then put 'create table chug.test as' ;
  else put 'union ' @ ;
  put 'select * from ' dataset ;
run;
proc sql ;
%include code ;
  ;
quit;

Not sure if PROC SQL code is the best code to generate. Why not just generate a SET statement?

filename code temp;
data _null_;
  file code ;
  set chug.claimants ;
  if _n_=1 then put 'set ';
  put dataset ;
run;
data chug.test ;
%include code ;
  ;
run;

You could also use call execute() to generate the code, but then there is no way to stop between the generation and execution phase and review the generated code. Or take advantage of the reporting writing features of the PUT statement.