Grouping values and sum up numeric varaibles

[Ronein]

Hello

I have a summary table that is called "table1"

I need to create a new summary table.

The new table will group some categories together and sum up the values.

In the result I expect to have following  categories (and for each category to sum customers and obligation values) 

0       a

0       b

0       All

2-5    a

6-7    All

8-10  All

11     a

11     b

11     All

0-11  All

12    a

12    b

12   all

I tried to do it via proc means with proc format but I didn't get the expected results

Data Tbl1;
input grade$   Model$  customers  obligation  ;
cards;
0    a     2000 5000
0    b	 5000 1000
0    All	 7000 6000
2    a	 2000 7000
3    a	 2000 4000
4    a	 2000 3000
5    a	 5000 2000
6    a	 2000 2000
7    a	 3000 1000
8    a	 4000 1000
9    a	 1000 2000
10   a     1000 1000
11   a     3000 3000
11   b     4000 1000
11   All	 7000 4000
0-11 All   36000 33000
12   a     5000 2000
12   b     2000 1000
12   All	 7000 3000
;
Run;


proc format;
value $grade
	'0'='a.0'
	'2','3','4','5'='b.2--5'
	'6','7' ='c.6--7'
	'8','9','10'='d.8--10'
	'11'='e.11'
	'0-11'='f.0-11'
	'12'='g.12'
	 ;
Run;


proc means data=Tbl1 nway noprint;
class grade  Model;
output out=xxx sum= ;
format grade $grade.;
run;

[PeterClemmensen]

I'm assuming that the only purpose of the a. b. notation is to order the formatted values properly?

[Ronein]

Yes.exactly. just for order of categories

[PeterClemmensen]

Ok. Does this meet your requirements?

Data Tbl1;
input grade$   Model$  customers  obligation  ;
cards;
0    a     2000 5000
0    b	 5000 1000
0    All	 7000 6000
2    a	 2000 7000
3    a	 2000 4000
4    a	 2000 3000
5    a	 5000 2000
6    a	 2000 2000
7    a	 3000 1000
8    a	 4000 1000
9    a	 1000 2000
10   a     1000 1000
11   a     3000 3000
11   b     4000 1000
11   All	 7000 4000
0-11 All   36000 33000
12   a     5000 2000
12   b     2000 1000
12   All	 7000 3000
;
Run;

proc format;
value $grade
	'0'='a.0'
	'2','3','4','5'='b.2--5'
	'6','7' ='c.6--7'
	'8','9','10'='d.8--10'
	'11'='e.11'
	'0-11'='f.0-11'
	'12'='g.12'
	 ;
Run;


proc means data=Tbl1 nway noprint order=formated;
class grade Model;
output out=xxx(drop=_TYPE_ _FREQ_) sum=;
format grade $grade.;
run;

[PeterClemmensen · Accepted solution]

Also, since the data seems to be in the order you want in your output already, you can define your PROC FORMAT like this and use ORDER=DATA in the PROC MEANS Statement..

proc format;
value $grade
	'0'='0'
	'2','3','4','5'='2--5'
	'6','7' ='6--7'
	'8','9','10'='8--10'
	'11'='11'
	'0-11'='0-11'
	'12'='12'
	 ;
Run;

proc means data=Tbl1 nway noprint order=data;
class grade Model;
output out=xxx(drop=_TYPE_ _FREQ_) sum=;
format grade $grade.;
run;