BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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

3 REPLIES 3
Steelers_In_DC
Barite | Level 11

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
Barite | Level 11

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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