DATA Step, Macro, Functions and more

Proc SQL Union based on table

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Proc SQL Union based on table

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

 


Accepted Solutions
Solution
‎11-24-2017 10:26 PM
Super User
Super User
Posts: 7,860

Re: Proc SQL Union based on table

[ Edited ]
Posted in reply to yashpande

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;

 

View solution in original post


All Replies
Super User
Posts: 5,829

Re: Proc SQL Union based on table

Posted in reply to yashpande
Why are you doing unions between rows from the same table?
Data never sleeps
Solution
‎11-24-2017 10:26 PM
Super User
Super User
Posts: 7,860

Re: Proc SQL Union based on table

[ Edited ]
Posted in reply to yashpande

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;

 

Esteemed Advisor
Posts: 5,409

Re: Proc SQL Union based on table

@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.

PG
Contributor
Posts: 52

Re: Proc SQL Union based on table

Yes this works. Perfect
Super User
Super User
Posts: 9,227

Re: Proc SQL Union based on table

Posted in reply to yashpande

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 223 views
  • 3 likes
  • 5 in conversation