Help using Base SAS procedures

Activity count in a table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

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

   30/08/2011  Facebook Click

   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  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  Display Click  

08/08/2011  Paid search Click  

08/08/2011  Facebook Click  

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

Date     Aggregator_click     Display_Click     Facebook_click     Paid_search_click

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
Super User
Posts: 6,500

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;

View solution in original post


All Replies
Solution
‎10-10-2011 08:44 AM
Super User
Super User
Posts: 6,500

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;

Respected Advisor
Posts: 3,777

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;

30/08/2011 Facebook Click

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 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 Display Click

08/08/2011 Paid search Click

08/08/2011 Facebook Click

08/08/2011 Facebook 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=_Smiley Happy;

          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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 201 views
  • 3 likes
  • 4 in conversation