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;
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;
I'm assuming that the only purpose of the a. b. notation is to order the formatted values properly?
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.