topic Re: How to add "Total" to the table in SAS Programming

How to add "Total" to the table

NazaninSAS — Fri, 28 Sep 2018 12:33:32 GMT

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

Re: How to add "Total" to the table

RW9 — Fri, 28 Sep 2018 12:50:44 GMT

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;

Re: How to add "Total" to the table

Panagiotis — Fri, 28 Sep 2018 12:51:51 GMT

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;

Re: How to add "Total" to the table

SuryaKiran — Fri, 28 Sep 2018 14:02:03 GMT

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;

Re: How to add "Total" to the table

Reeza — Fri, 28 Sep 2018 15:40:17 GMT

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.

Re: How to add "Total" to the table

NazaninSAS — Fri, 28 Sep 2018 15:43:07 GMT

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

Re: How to add "Total" to the table

Reeza — Fri, 28 Sep 2018 16:02:13 GMT

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?

Re: How to add "Total" to the table

Tom — Sat, 29 Sep 2018 04:05:24 GMT

Why not just run PROC FREQ?

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

Re: How to add "Total" to the table

NazaninSAS — Mon, 01 Oct 2018 13:28:33 GMT

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	*=E20B20**	=IF(F20-C20<0,0,F20-C20)

do you have any suggestions?

Thanks,

Nazanin

Re: How to add "Total" to the table

Reeza — Mon, 01 Oct 2018 14:18:33 GMT

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;

Re: How to add "Total" to the table

NazaninSAS — Mon, 01 Oct 2018 14:20:30 GMT

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

Re: How to add "Total" to the table

Reeza — Mon, 01 Oct 2018 14:27:00 GMT

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

Re: How to add "Total" to the table

NazaninSAS — Mon, 01 Oct 2018 14:30:42 GMT

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.

Re: How to add "Total" to the table

Reeza — Mon, 01 Oct 2018 14:53:55 GMT

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.

Re: How to add "Total" to the table

NazaninSAS — Mon, 01 Oct 2018 14:56:23 GMT

E is fixed for each EEOG.

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

Re: How to add "Total" to the table

Reeza — Mon, 01 Oct 2018 15:11:21 GMT

Then you can add a CASE statement in to assign the values or store it in a table and merge it in.

@NazaninSAS wrote:

E is fixed for each EEOG.

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

Re: How to add "Total" to the table

NazaninSAS — Mon, 01 Oct 2018 16:14:45 GMT

Thanks for your great help.

I don't know why when I run the code, I receive errors.

Re: How to add "Total" to the table

Reeza — Mon, 01 Oct 2018 16:19:48 GMT

Post the code and errors. It works on the sample data you provided.

Is it not working in terms of incorrect results or just not working.

Re: How to add "Total" to the table

NazaninSAS — Mon, 01 Oct 2018 16:23:02 GMT

data have;

infile cards truncover;;

input PRI EEOG $ PWD $

cards;

1 7

2 3 DIS

3 7

4 3

5 2

6 7

7 7

8 10 DIS

9 7

10 3

11 7

;

run;

proc sql;

create table want as

select EEOG,

count(*) as B, sum(PWD='DIS') as C,

(calculated C/calculated B) as D format=percent 12.1,

0.023 as E

calculated B* calculated E as F,

case

when calculated F-calculated C<0 then 0

else calculated F-calculated C

end as G

from have

group by EEOG

union

select 'Total' as EEOG,

count (*) as B,

sum (PWD='DIS') as C,

(calculated C / calculated B) as D format=percent 12.1,

0.023 as E,

calculated B* calculated E as F,

case

when calculated F-calculated C<0 then 0

else calculated F-calculated C

end as G

from have;

quit;

------------------------------------------------

ERROR 22-322: Expecting a format name.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

359 0.023 as E

360 calculated B* calculated E as F,

----------

ERROR 78-322: Expecting a ','.

ERROR 76-322: Syntax error, statement will be ignored.

361 case

362 when calculated F-calculated C<0 then 0

363 else calculated F-calculated C

364 end as G

365

366 from have

367 group by EEOG

368 union

369 select 'Total' as EEOG,

370 count (*) as B,

371 sum (PWD='DIS') as C,

372 (calculated C / calculated B) as D format=percent 12.1,

373 0.023 as E,

374 calculated B* calculated E as F,

375 case

376 when calculated F-calculated C<0 then 0

377 else calculated F-calculated C

378 end as G

379 from have;

380 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

Re: How to add "Total" to the table

NazaninSAS — Mon, 01 Oct 2018 16:23:42 GMT

I hope you don't laugh at me! I'm totally new to SAS