I want to sum a columns value in the bottom of the table from a computed sum grouped by a column.
PROC SQL;
CREATE TABLE Test AS
SELECT
t1.Groupingvariabel AS Group,
SUM(t1.1) FORMAT NLNUM25. AS 1,
SUM(t1.2) FORMAT NLNUM25. AS 2,
SUM(t1.3) FORMAT NLNUM25. AS 3,
SUM(t1.4) FORMAT NLNUM25. AS 4
FROM Dataset AS t1
GROUP BY t1.Groupingvariabel;
QUIT;
Here I want to make an grand total for each of the variabel as a last row in the data set. I want to make it in one step, is it possible and then how? So I want a total row in the end of the dataset that sum each observation in each of the variabel 1, 2, 3 and 4 separately.
Hi @OskarBlad
I agree with @Reeza , but if you want to use SQL, you can make the table as follows:
data have (drop=i);
do Groupingvariabel = 'Gruppe1', 'Gruppe2', 'Gruppe3', 'Gruppe4';
do i = 1 to 10;
t1 = int(ranuni(3)*100);
t2 = int(ranuni(3)*100);
t3 = int(ranuni(3)*100);
t4 = int(ranuni(3)*100);
output;
end;
end;
run;
proc sql;
create table want (drop=type) as
select
Groupingvariabel,
sum(t1) as t1,
sum(t2) as t2,
sum(t3) as t3,
sum(t4) as t4,
1 as type
from have
group by Groupingvariabel
union
select
'Total' as Groupingvariabel,
sum(t1) as t1,
sum(t2) as t2,
sum(t3) as t3,
sum(t4) as t4,
2 as type
from have
order by
type,
Groupingvariabel;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.