SQL vertical join

[Batman]

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;

[PeterClemmensen]

How do you define a Vertical Join?

[Batman]

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

[PGStats]

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.

[Batman]

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)?

[PGStats · Accepted solution]

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.