## Activity count in a table

Solved
Occasional Contributor
Posts: 13

# Activity count in a table

Hello,

I have a two column table(an excerpts below), the first column is date while the second is activity. There are four different activities in the activity column (Aggregator click, Display click, Facebook click and Paid Search Click).

Date                 Activity

05/08/2011  Display Click

28/08/2011  Paid search Click

29/08/2011  Display Click

29/08/2011  Display Click

28/08/2011  Aggregator Click

09/08/2011  Aggregator Click

08/08/2011  Aggregator Click

05/08/2011  Paid search Click

07/08/2011  Paid search Click

18/08/2011  Paid search Click

30/08/2011  Aggregator Click

03/08/2011  Paid search Click

08/08/2011  Display Click

08/08/2011  Display Click

08/08/2011  Paid search Click

08/08/2011  Paid search Click

08/08/2011  Paid search Click

08/08/2011  Paid search Click

08/08/2011  Paid search Click

08/08/2011  Paid search Click

10/08/2011  Aggregator Click

10/08/2011  Aggregator Click

I will like to transform the table into five column table, where each activity is counted per day and return something in the nature of below example;

03/08/2011     2                         4                    0                              6

I will very much appreciate any help.

Accepted Solutions
Solution
‎10-10-2011 08:44 AM
Super User
Posts: 8,083

## Activity count in a table

You can use summary to count and then transpose the results.

proc summary data=have nway ;

class date activity;

output out=counts;

run;

proc transpose data=counts out=want(drop=_name_) ;

by date ;

id activity;

var _freq_;

run;

Do get the missing values converted to zeros you might want to add a final data step.

data want;

set want;

array n _numeric_;

do over n; n=sum(n,0); end;

run;

All Replies
Solution
‎10-10-2011 08:44 AM
Super User
Posts: 8,083

## Activity count in a table

You can use summary to count and then transpose the results.

proc summary data=have nway ;

class date activity;

output out=counts;

run;

proc transpose data=counts out=want(drop=_name_) ;

by date ;

id activity;

var _freq_;

run;

Do get the missing values converted to zeros you might want to add a final data step.

data want;

set want;

array n _numeric_;

do over n; n=sum(n,0); end;

run;

Posts: 3,852

## Re: Activity count in a table

`Tom wrote:Do get the missing values converted to zeros you might want to add a final data step.data want;  set want;  array n _numeric_;  do over n; n=sum(n,0); end;run;`

In this situation the COMPLETETYPES option will provide the zeros saving a bit of work.

`proc summary completetypes nway;   class date activity;   output;   run;`
Occasional Contributor
Posts: 16

## Activity count in a table

I think this is what you're looking for:

data aaa;

infile datalines truncover;

input Date ddmmyy10. Activity \$30.;

format date ddmmyy10.;

datalines;

05/08/2011 Display Click

28/08/2011 Paid search Click

29/08/2011 Display Click

29/08/2011 Display Click

28/08/2011 Aggregator Click

09/08/2011 Aggregator Click

08/08/2011 Aggregator Click

05/08/2011 Paid search Click

07/08/2011 Paid search Click

18/08/2011 Paid search Click

30/08/2011 Aggregator Click

03/08/2011 Paid search Click

08/08/2011 Display Click

08/08/2011 Display Click

08/08/2011 Paid search Click

08/08/2011 Paid search Click

08/08/2011 Paid search Click

08/08/2011 Paid search Click

08/08/2011 Paid search Click

08/08/2011 Paid search Click

10/08/2011 Aggregator Click

10/08/2011 Aggregator Click

;

proc sql;

create table ccc as

select date,Activity, count(*) as num_clicks

from bbb

group by date, activity

order by date, activity;

quit;

proc transpose data=ccc out=ddd(drop=_;

by date;

id activity;

var num_clicks;

run;

data eee;

set ddd;

drop i;

array num{*} _numeric_;

do i= 1 to dim(num);

num{i}=coalesce(num{i},0);

end;

run;

\Hobbes

🔒 This topic is solved and locked.