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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
Tom
Super User Tom
Super User

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;

data_null__
Jade | Level 19

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;

Hobbes
Calcite | Level 5

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=_:);

          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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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