topic Summing categories and putting the sum in new variables in SAS Data Management

Summing categories and putting the sum in new variables

ilikesas — Fri, 29 May 2015 15:37:12 GMT

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!

Re: Summing categories and putting the sum in new variables

Steelers_In_DC — Fri, 29 May 2015 16:29:49 GMT

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;

Re: Summing categories and putting the sum in new variables

ilikesas — Sun, 31 May 2015 02:36:05 GMT

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!

Re: Summing categories and putting the sum in new variables

PGStats — Sun, 31 May 2015 02:49:53 GMT

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