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

Solved
Super Contributor
Posts: 459

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

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!

Accepted Solutions
Solution
‎12-02-2014 05:43 PM
Posts: 1,270

## Re: How to create a "working" summary table from a bigger table

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;

All Replies
Solution
‎12-02-2014 05:43 PM
Posts: 1,270

## Re: How to create a "working" summary table from a bigger table

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;

Super Contributor
Posts: 459

## Re: How to create a "working" summary table from a bigger table

Hi stat@sas

thanks for the solution!!!

🔒 This topic is solved and locked.