Summing categories and putting the sum in new variables

[ilikesas]

Hi,

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

company	date	director	category
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

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

Header 1	Header 2	category1	category2	category3
A	30/12/2000	3	1	2

Thank you!

[Steelers_In_DC · Accepted solution]

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;

[ilikesas]

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!

[PGStats]

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