topic SQL vertical join in New SAS User

SQL vertical join

Batman — Tue, 29 Oct 2019 21:24:21 GMT

Can I use functions with an sql vertical join? Something such as

proc sql;

select a, sum(b) as tot_b

from X

union

select a, sum(b) as tot_b

from Y

group by a;

Re: SQL vertical join

PeterClemmensen — Tue, 29 Oct 2019 21:26:55 GMT

How do you define a Vertical Join?

Re: SQL vertical join

Batman — Tue, 29 Oct 2019 21:29:06 GMT

The set operator "union" allows you to append two datasets in SQL

Re: SQL vertical join

PGStats — Tue, 29 Oct 2019 21:53:05 GMT

Yes, you can use summary functions. Note that each subquery is processed separately. In your example, the group by clause will refer only to the second subquery. Without a group by clause, the first subquery will remerge tot_b with every row from table X.

Re: SQL vertical join

Batman — Thu, 31 Oct 2019 17:30:02 GMT

If there are some values of variable "a" that are in both input datasets X and Y, can values of variable "b" be grouped by "a" in the same query (i.e. the one where they are joined)?

Re: SQL vertical join

PGStats — Thu, 31 Oct 2019 18:13:26 GMT

First, there is no such thing as a "vertical join". UNION ALL does a concatenation of tables. UNION without ALL eliminates duplicates. CORR requests matching by variable names (only same named variables are concatenated).

So it seems like you want to concatenate X and Y first and then GROUP BY to get the sums. Something like:

proc sql;
select  a, sum(b) as tot_b
from 
  (select a, b
   from X
   union all corr
   select a, b
   from Y)
 group by a;
 quit;

Untested. Not sure about the use of parentheses here. You might have to experiment.