## Summing categories and putting the sum in new variables

Solved
Super Contributor
Posts: 459

# Summing categories and putting the sum in new variables

Hi,

suppose that for a given company-date combination I have the following info about the directors and categories:

companydatedirectorcategory
A30/12/2000John1
A30/12/2000John2
A30/12/2000John3
A30/12/2000Bill1
A30/12/2000Bill3
A30/12/2000Suzan1

What I would like to get is the total sum of each category into new variables (for each company-date combination):

A30/12/2000312

Thank you!

Accepted Solutions
Solution
‎05-29-2015 12:29 PM
Valued Guide
Posts: 863

## Re: Summing categories and putting the sum in new variables

Here you go:

data have;

infile cards dsd;

informat date ddmmyy10.;

format date mmddyy10.;

input company \$ date director \$ category;

cards;

A,30/12/2000,John,1

A,30/12/2000,John,2

A,30/12/2000,John,3

A,30/12/2000,Bill,1

A,30/12/2000,Bill,3

A,30/12/2000,Suzan,1

;

run;

proc sql;

create table prep as

select distinct company,date,category,count(category) as count

from have

group by category

order by category;

proc transpose data=prep out=want(drop=_name_) prefix=category;

by company date;

var count;

id category;

idlabel category;

run;

All Replies
Solution
‎05-29-2015 12:29 PM
Valued Guide
Posts: 863

## Re: Summing categories and putting the sum in new variables

Here you go:

data have;

infile cards dsd;

informat date ddmmyy10.;

format date mmddyy10.;

input company \$ date director \$ category;

cards;

A,30/12/2000,John,1

A,30/12/2000,John,2

A,30/12/2000,John,3

A,30/12/2000,Bill,1

A,30/12/2000,Bill,3

A,30/12/2000,Suzan,1

;

run;

proc sql;

create table prep as

select distinct company,date,category,count(category) as count

from have

group by category

order by category;

proc transpose data=prep out=want(drop=_name_) prefix=category;

by company date;

var count;

id category;

idlabel category;

run;

Super Contributor
Posts: 459

## Re: Summing categories and putting the sum in new variables

Hi Mark, thank you for the code!

With a small modification I ran the code and it gave me the result that I wanted (in the proc sql step I also grouped by company and date).

thanks again!

Posts: 5,487

## Re: Summing categories and putting the sum in new variables

Good! Note that grouping by company, date and category is more efficient (no remerging needed) and will not require the distinct predicate in the select clause. - PG

PG
🔒 This topic is solved and locked.