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!
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;
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;
Hi stat@sas
thanks for the solution!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.