How to create a "working" summary table from a bigger table

[ilikesas]

Hi,

suppose I have the following file:

date	company	name	category1	category2	category3	category4
2010	ABC	a	1	0	0	0
2010	ABC	b	0	1	0	0
2010	ABC	c	0	0	1	0
2009	ABC	a	1	0	0	0
2009	ABC	b	0	1	0	0
2009	ABC	c	0	0	1	0
2010	DEF	d	0	0	1	0
2010	DEF	e	0	0	0	1
2010	DEF	f	1	0	0	0
2009	DEF	d	0	0	1	0
2009	DEF	e	0	0	0	1
2009	DEF	f	1	0	0	0

For each company in each year, I have several names and the corresponding category of each name.

Now out of this table I would like to create the following table:

date	company	total1	total2	total3	total4
2010	ABC	1	1	1	0
2009	ABC	1	1	1	0
2010	DEF	1	0	1	1
2009	DEF	1	0	1	1

Here for each company in each year I would like to have the TOTAL sum of the categories corresponding to that particular year and company. So for example, in the first table company ABC in date 2010 had a total of 1 name of category1, 1 name of category2, 1 name of category3 and 0 names of category4, and so this is reflected in the first row of the second table.

And so on for the other years and companies like this I can make a regression on the new table.

Thank you!

[stat_sas · Accepted solution]

proc sql;

create table want as

select date,company,

sum(category1) as total1,

sum(category2) as total2,

sum(category3) as total3,

sum(category4) as total4

from have group by date,company;

quit;

[ilikesas]

Hi stat@sas

thanks for the solution!!!