topic Re: Grouping values and sum up numeric varaibles in SAS Programming

Grouping values and sum up numeric varaibles

Ronein — Sun, 27 Jan 2019 08:50:08 GMT

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;

Re: Grouping values and sum up numeric varaibles

PeterClemmensen — Sun, 27 Jan 2019 09:16:36 GMT

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

Re: Grouping values and sum up numeric varaibles

Ronein — Sun, 27 Jan 2019 09:28:17 GMT

Yes.exactly. just for order of categories

Re: Grouping values and sum up numeric varaibles

PeterClemmensen — Sun, 27 Jan 2019 10:07:04 GMT

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;

Re: Grouping values and sum up numeric varaibles

PeterClemmensen — Sun, 27 Jan 2019 10:08:42 GMT

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;