topic Re: Add few calculated observations in a result table in SAS Programming

Add few calculated observations in a result table

Barkat — Wed, 17 Nov 2021 19:01:04 GMT

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,

Re: Add few calculated observations in a result table

PeterClemmensen — Wed, 17 Nov 2021 19:09:32 GMT

So you ave table1 and want table2, correct?

Re: Add few calculated observations in a result table

Barkat — Wed, 17 Nov 2021 19:12:07 GMT

That is correct. Thanks for looking into this.

Re: Add few calculated observations in a result table

Barkat — Wed, 17 Nov 2021 19:14:40 GMT

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

Re: Add few calculated observations in a result table

Reeza — Wed, 17 Nov 2021 20:04:54 GMT

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,

Re: Add few calculated observations in a result table

Barkat — Wed, 17 Nov 2021 21:41:54 GMT

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

Re: Add few calculated observations in a result table

Tom — Wed, 17 Nov 2021 21:55:05 GMT

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;

Re: Add few calculated observations in a result table

Reeza — Wed, 17 Nov 2021 22:43:26 GMT

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.