topic Activity count in a table in SAS Procedures

Activity count in a table

Osebeyo — Mon, 10 Oct 2011 11:35:58 GMT

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

30/08/2011 Facebook Click

05/08/2011 Display Click

28/08/2011 Paid search 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 Facebook 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 Paid search Click

08/08/2011 Facebook Click

08/08/2011 Paid search 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;

Date Aggregator_click Display_Click Facebook_click Paid_search_click

03/08/2011 2 4 0 6

I will very much appreciate any help.

Activity count in a table

Tom — Mon, 10 Oct 2011 12:44:24 GMT

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;

Activity count in a table

Hobbes — Mon, 10 Oct 2011 12:57:26 GMT

I think this is what you're looking for:

data aaa;

infile datalines truncover;

input Date ddmmyy10. Activity $30.;

format date ddmmyy10.;

datalines;

30/08/2011 Facebook Click

05/08/2011 Display Click

28/08/2011 Paid search 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 Facebook 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 Paid search Click

08/08/2011 Facebook Click

08/08/2011 Paid search 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

Re: Activity count in a table

data_null__ — Mon, 10 Oct 2011 13:09:19 GMT

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;