Proc SQL Union based on table

[yashpande]

Hi All,

I have table which provides values as input to other queries to create table.

E.g.

Input table

abc

Query :

Proc sql;

create table output as select column1 from lib.abc quit;

If Input table has 2 or more rows like this 

Input table

abc

def

Query :

Proc sql;

create table output as (select column1 from lib.abc) union (select column1 from lib.def ); quit;

somehow I am not able to solve this using macro.

I tried creating macro as below

Proc sql;

select column1 into :mac separated by ' union' from Input_table ; quit;

above works for multiple tables but fails when there is only 1 value.

Please help in solving this

[LinusH]

Why are you doing unions between rows from the same table?

[Tom · Accepted solution]

Can you provide an example of the input metadata that you have and the generated code that you want to create?

You might be able to do it with macro variables, but remember that macro variables are limited to 64K bytes each. So if your metadata is really large it might not work.  Also I think that you cannot really reference more than about 10 to 16 tables in a single SQL statement.

Here is what it looks like you are asking for.

data have ;
  input memname $32. ;
cards;
abc
def
;

proc sql noprint ;
select "select var1 from lib."||memname
  into :code separated by ' union '
  from have
;
create table want as
&code
;
quit;

Although it seems to me it would be easier to generate SAS code instead of SQL code. You will also hit fewer resource limits.

proc sql noprint ;
select 'lib.'||memname
  into :dslist separated by ' '
  from have
;
quit;

data want;
  set &dslist ;
run;

[PGStats]

@Tom, "PROC SQL can process a maximum of 256 tables for a join" I don't know is that limit applies to UNIONs. If duplicates are not a problem, UNION ALL would be more efficient than UNION.

[yashpande]

Yes this works. Perfect

[RW9]

I agree with @LinusH here, the process you present does not make sense.  Please elaborate with some test data and what you want out, going down your path is going to be resource heavy.  If you just need to append all datasets - and they are all exactly the same:

data _null_;
  set sashelp.vtable (where=(libname="<yourlib>")) end=last;
  if _n_=1 then call execute('data want; set <yourlib>.'||strip(memname));
  else call execute(' '||strip(memname));
  if last then call execute(';run;');
run;

But its really not the best path, a change to the process should avoid this.