SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

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

Hi,

suppose I have the following file:

datecompanynamecategory1category2category3category4
2010ABCa1000
2010ABCb0100
2010ABCc0010
2009ABCa1000
2009ABCb0100
2009ABCc0010
2010DEFd0010
2010DEFe0001
2010DEFf1000
2009DEFd0010
2009DEFe0001
2009DEFf1000

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:

datecompanytotal1total2total3total4
2010ABC1110
2009ABC1110
2010DEF1011
2009DEF1011

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
Trusted Advisor
Posts: 1,204

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;

View solution in original post


All Replies
Solution
‎12-02-2014 05:43 PM
Trusted Advisor
Posts: 1,204

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: 413

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

Hi stat@sas

thanks for the solution!!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 249 views
  • 0 likes
  • 2 in conversation