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;
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.
How do you define a Vertical Join?
The set operator "union" allows you to append two datasets in SQL
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.
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)?
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.