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

Summing categories and putting the sum in new variables

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

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

Header 1Header 2category1category2category3
A30/12/2000312

Thank you!


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

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;

View solution in original post


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

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

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!

Respected Advisor
Posts: 4,651

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.

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

Discussion stats
  • 3 replies
  • 387 views
  • 0 likes
  • 3 in conversation