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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.