How to add "Total" to the table

[Reeza]

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.

---

[NazaninSAS]

Thanks for your great help.

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

[Reeza]

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. 

[NazaninSAS]

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,

----------

78

76

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

[NazaninSAS]

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

[Reeza]

Format=percent12.1 -> no spaces between percent and 12.

[NazaninSAS]

Thanks Reeza,

I was able to run it, but my problem is, the output only shows the total.

Can you please send me your output?

Thanks,

Nazanin

[NazaninSAS]

I want to generate this table:

Column A	Column B	Column C	Column D	Column E	Column F	Column G
	Total	Subset		Fixed percentage for each EEOG
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)

[Reeza · Accepted solution]

CHECK YOUR LOG and fix errors in the order they appear.

You'll see you missed a semicolon on the INPUT statement as well so your data never read in correctly. You excluded that part of the log and errors so I didn't notice it either.

You also have some typos in your code. Compare it line to line to mine (look for missing comma's at the end and spaces) then you'll get the correct results.

[NazaninSAS]

That is just fantastic!

Thanks a lot.

How can I somehow show EEOG10 before total?

I also have to assign these numbers to column E based on different EEOG

for example, column E should be 0.043 for EEOG02

0.038 for EEOG03 and 0.046 for EEOG04.

Best regards,

Nazanin

[Reeza]

Add another CASE statement based on the EEOG values that will assign the correct value.

---

@NazaninSAS wrote:

That is just fantastic!

Thanks a lot.

How can I somehow show EEOG10 before total?

I also have to assign these numbers to column E based on different EEOG

for example, column E should be 0.043 for EEOG02

0.038 for EEOG03 and 0.046 for EEOG04.

Best regards,

Nazanin

---

[NazaninSAS]

Hi Reeza,

I added the "case". can you please check and see where I'm wrong?

Thanks

Nazanin

data have;

infile cards truncover;;

input PRI EEOG $ PWD $;

cards;

1 EEOG7

2 EEOG3 DIS

3 EEOG7

4 EEOG3

5 EEOG2

6 EEOG7

7 EEOG7

8 EEOG10 DIS

9 EEOG7

10 EEOG3

11 EEOG7

;

run;

proc sql;

create table want as

select EEOG,

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

(calculated C/calculated B) as D format=percent12.1,

Select EEOG

Case

when 2 then 0.043

when 3 then 0.038

when 4 then 0.046

when 5 then 0.139

when 7 then 0.034

when 10 then 0.07

else 0.04

end as E

from have

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=percent12.1,

0.043 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;

[Reeza]

Case when EEOG=2 then 0.04

         When EEOG=3 then 0.005

...

...

else 0

end as MulitplyFactor