How to add "Total" to the table

[NazaninSAS]

Hi,

I have this code:

proc sql;

title " EEOG";

select EEOG, count(PRI) as Count

from EmployeeTest.Sheet1

group by EEOG

order by EEOG;

quit;

title;

the program runs correctly, but I want to add the total at the bottom of the table. how can I do it?

Thanks,

Nazanin

[RW9]

Well, I wouldn't be creating a report from SQL, use proc print or report for that kind of thing.  As such this is untested:

title " EEOG";

proc sql;
  select eeog, 
         count(pri) as count
  from   employeetest.sheet1
  group by eeog
  union all
  select "Total",
         count(pri)
  from   employeetest.sheet1;
quit;

[Panagiotis]

Not sure if you can do it in SQL, but here are two options that might work.

1. Creates a macro variable and uses that as a footnote with the total. Not sure if this will work for you since you want a row.

proc sql noprint;
	select sum(mpg_city) as total
	 into :total /*Create macro variable total with the total count*/
	from sashelp.cars;
quit;
%put &=total; /*View macro variable in the log. See there are leading spaces*/
%let total=&total; /*Set macro variable to itself and removing leading spaces*/
%put &=total; /*View macro variable in the log to ensure it's correct*/

footnote "The total MPG_City is: &total"; /*Add macro variable to footnote*/
proc sql ;
	select make, count(make) as count
	from sashelp.cars
	group by make
	order by make;
quit;
footnote;

2. This will combine a few things. I got this from here: Insert a Row at the end of the data with totals so it's not my solution. Pretty cool way of doing it, I learned something new.

/*Create the total row*/
proc means noprint data=sashelp.cars ;
  output out=summary(keep=mpg_city) sum=mpg_city;
run;

/*Create the necessary table using your proc sql*/
proc sql;
	create table test as
		select make, count(make) as count
		from sashelp.cars
		group by make
		order by make;
quit;

/*Combine the tables*/
data want ;
  set test summary(in=in2); /*concatenate the tables. In= option creates a temp variable called in2*/
  if in2=1 then Make='TOTAL'; /*In2 will =1 when SAS brings in the row from summary. This will allow us to change the column name we want to "Total"*/
run;

[SuryaKiran]

PROC REPORT might be the right tool for you. Check this example.

proc report data=sashelp.class nowd;
column sex newsex N;
define sex/group noprint;
define newsex/computed format=$8.;
define n/'Count';
compute n;
if _break_="" then cnt+1;
endcomp;
compute newsex / char;
      if Sex ne "" then newsex=sex;
      else newsex=' ';
      if _break_='_RBREAK_' then newsex="total";
   endcomp;
rbreak after/summarize;
run;

[Reeza]

In general, it's not a good idea to store totals/subtotals in your data sets, instead, it's a better idea to let the reporting procedure generate it for you as well as display it into Excel or whatever type of report you're building.

[NazaninSAS]

so I have to export the table to Excel and calculate the total there?

[Reeza]

NO. But you don't store the totals in the table, you generate it at the time of reporting.. 

ods excel file='path to my file.xlsx' style=meadow;

proc tabulate data=sashelp.shoes ;
class region subsidiary;
var Stores Sales Inventory Returns;
table (region*subsidiary all), Stores='# of Stores'*Sum=''*f=8. 
                         Sales*Sum=''*f=dollar32.
                         Inventory*Sum=''*f=dollar32.
                         Returns*Sum=''*f=dollar32.;
run;

ods excel close;

---

@NazaninSAS wrote:

so I have to export the table to Excel and calculate the total there?

---

[Tom]

Why not just run PROC FREQ?

proc freq data=EmployeeTest.Sheet1 ;
  tables EEOG;
run;

[NazaninSAS]

Thanks,

However, I need to keep the total at the bottom.

Eventually, I need to produce the following table: so far, I have figured out two first columns.

Column A	Column B	Column C	Column D	Column E	Column F	Column G
	Total	Subset		Fixed percentage
EEOG	#	#	%	%
2	1417	76	=C14/B14	0.043	=B14*E14	=IF(F14-C14<0,0,F14-C14)
3	6900	417	=C15/B15	0.038	=B15*E15	=IF(F15-C15<0,0,F15-C15)
4	40	5	=C16/B16	0.046	=B16*E16	=IF(F16-C16<0,0,F16-C16)
5	79	6	=C17/B17	0.139	=B17*E17	=IF(F17-C17<0,0,F17-C17)
7	1871	148	=C18/B18	0.034	=B18*E18	=IF(F18-C18<0,0,F18-C18)
10	770	56	=C19/B19	0.07	=B19*E19	=IF(F19-C19<0,0,F19-C19)
Total	=SUM(B14:B19)	=SUM(C14:C19)	=C20/B20	0.04	=E20*B20	=IF(F20-C20<0,0,F20-C20)

do you have any suggestions?

Thanks,

Nazanin

[Reeza]

The first three columns would come out by default from PROC FREQ or MEANS. You never showed us what your starting data looks like. That would help, otherwise you can use your output from PROC FREQ/TABULATE into a data step for the last three columns which is almost exactly like you typed it out.

data want;
set have;
f=b*3;
g=ifn(f-c<0, 0, f-c);
run;

[NazaninSAS]

Thanks,

This is my original data:

PRI	EEOG	PWD
1	7
2	3	DIS
3	7
4	3
5	2
6	7
7	7
8	10	DIS
9	7
10	3
11	7

[Reeza]

I'm not seeing the connection between your input data and expected output. Your output table is using just column references ABCDEF which doesn't align with the data fields so no idea how it maps.....for the given input below what is the exact output you'd expect. If your use case can cover several variations make sure to include them in your sample data.

---

@NazaninSAS wrote:

Thanks,

This is my original data:

PRI	EEOG	PWD
1	7
2	3	DIS
3	7
4	3
5	2
6	7
7	7
8	10	DIS
9	7
10	3
11	7

---

[NazaninSAS]

this table is my DB, SAS should count the total in each EEOG from this DB, and then generate column B, then it should look at where the condition is DIS, and generate the column C. Rest of columns are the calculations based on column B and C.

[Reeza]

Here's one way, but it depends on how that E value is calculated. You said it was fixed before, but it doesn't look like it's fixed in this example.

[NazaninSAS]

E is fixed for each EEOG.

for example, for EEOG 02, the number should be 0.043.