topic Re: Create Grand Total Sum SQL in SAS Programming

Create Grand Total Sum SQL

OskarBlad — Tue, 25 Jun 2019 15:44:29 GMT

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.

Re: Create Grand Total Sum SQL

Reeza — Tue, 25 Jun 2019 15:56:32 GMT

Is this for a data set or or a report. For reports it's very trivial, for data sets it's more of a pain. And are you set on using SQL? PROC MEANS is better as is PROC REPORT and TABULATE.

Re: Create Grand Total Sum SQL

ErikLund_Jensen — Tue, 25 Jun 2019 16:10:27 GMT

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;