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.
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;
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.