Add few calculated observations in a result table

[Barkat]

From a large dataset using the following code I get a table (see table-1). But I would like to get a table that contains some calculated observations (see table-2).

proc freq data=have;
table Year*Location / nocum nocol norow nopercent ;
run;

Table-1
	A	B	C	D	E	F	G
2001	100	299	399	140	29	1	3
2002	105	251	356	93	39	0	2
2003	79	145	224	32	19	1	5
2004	186	322	508	89	24	2	4
2005	220	315	535	107	67	7	6
2006	108	152	260	95	45	11	2
2007	53	56	109	72	45	10	3
2008	18	7	25	35	18	0	0
2009	15	20	35	42	26	6	1
2010	66	103	169	99	58	15	1

Table-2

	A	B	C	D	E	F	G	AtoG	A+B	C+D+E+F+G
2001	100	299	399	140	29	1	3	971	399	572
2002	105	251	356	93	39	0	2	846	356	490
2003	79	145	224	32	19	1	5	505	224	281
2004	186	322	508	89	24	2	4	1,135	508	627
2005	220	315	535	107	67	7	6	1,257	535	722
2006	108	152	260	95	45	11	2	673	260	413
2007	53	56	109	72	45	10	3	348	109	239
2008	18	7	25	35	18	0	0	103	25	78
2009	15	20	35	42	26	6	1	145	35	110
2010	66	103	169	99	58	15	1	511	169	342

Thanks,

[PeterClemmensen]

So you ave table1 and want table2, correct?

[Barkat]

That is correct. Thanks for looking into this.

[Barkat]

Actually, the table-1 was produced after running the code that I mentioned. I would like to create a code that will produce table-2

[Reeza · Accepted solution]

PROC TABULATE with a Multilevel format.

data have;
input Year    Area $    Profit;
cards;
2001    A   1
2002    A   2
2001    B   1
2001    C   3
2002    C   1
2001    E   4
2002    E   2
2001    F   3
2002    F   4
;;;;
run;

*format - groups A/B/C into D and E/F into G as well;
proc format;
value $ area_fmt (multilabel)
'A' = 'A'
'B' = 'B'
'C' = 'C'
'A', 'B', 'C' = 'D'
'E' = 'E'
'F' = 'F'
'E', 'F' = 'G';
run;

*summary statistics - not CLASS and FORMAT statements;
proc means data=have noprint nway;
class year area / mlf;
format area $area_fmt.;
var profit;
output out=want sum(profit)=profit;
run;

*show results;
proc print data=want;
run;

---

@Barkat wrote:

From a large dataset using the following code I get a table (see table-1). But I would like to get a table that contains some calculated observations (see table-2).

proc freq data=have;
table Year*Location / nocum nocol norow nopercent ;
run;

Table-1
	A	B	C	D	E	F	G
2001	100	299	399	140	29	1	3
2002	105	251	356	93	39	0	2
2003	79	145	224	32	19	1	5
2004	186	322	508	89	24	2	4
2005	220	315	535	107	67	7	6
2006	108	152	260	95	45	11	2
2007	53	56	109	72	45	10	3
2008	18	7	25	35	18	0	0
2009	15	20	35	42	26	6	1
2010	66	103	169	99	58	15	1

Table-2

	A	B	C	D	E	F	G	AtoG	A+B	C+D+E+F+G
2001	100	299	399	140	29	1	3	971	399	572
2002	105	251	356	93	39	0	2	846	356	490
2003	79	145	224	32	19	1	5	505	224	281
2004	186	322	508	89	24	2	4	1,135	508	627
2005	220	315	535	107	67	7	6	1,257	535	722
2006	108	152	260	95	45	11	2	673	260	413
2007	53	56	109	72	45	10	3	348	109	239
2008	18	7	25	35	18	0	0	103	25	78
2009	15	20	35	42	26	6	1	145	35	110
2010	66	103	169	99	58	15	1	511	169	342

Thanks,

---

[Barkat]

Sorry, that my question was not clear, I guess. Assume the table-1 was created from a large dataset of thousands of people who appeared in those locations (A to G) in different dates during the years 2001 to 20010. What SAS program I need to use, so I can create table-2

[Tom]

If you are starting with TABLE1 as input (that is you have already calculate the counts into individual variables) then making TABLE2 is much easier.  You already provide the basic code in your table headers.

data want;
  set have;
  sum1 = sum(of a--g);
  sum2 = sum(a,b);
  sum3 = sum(c,d,e,f,g);
  label sum1='AtoG' sum2='A+B' sum3='C+D+E+F+G';
run;

[Reeza]

You use the exact same program but apply a MLF format to your variable to get the extra categories. If you look closely at the example, note the format that has multiple groups. When you run the examples, do you see the different levels that are summarized at once with that single option?

You can post process the data as Tom suggests, but I usually prefer to do it in a single step.