How to count number of repeated value of a variable for each group

[owenwqp1]

Dear all,

     I want to count how many days in a week is sunny, rainy or cloudy for each city. Anyone could give me some suggestion? Thanks!

The original data is as follows:

cityid	weather
1	sunny
1	sunny
1	cloudy
1	rainy
1	rainy
1	cloudy
1	sunny
2	rainy
2	rainy
2	sunny
2	sunny
2	sunny
2	cloudy
2	cloudy

What I really want the data to be is as follows:

cityid	weather	count
1	sunny	3
1	sunny	3
1	cloudy	2
1	rainy	2
1	rainy	2
1	cloudy	2
1	sunny	3
2	rainy	2
2	rainy	2
2	sunny	4
2	sunny	4
2	sunny	4
2	sunny	4
2	cloudy	1

[Astounding]

First, add a variable to your data set so you can sort the observations, but later put them back into their original order.

Here's a program that will get you halfway there.  It will produce the counts and put them into a separate data set. 

proc freq data=have noprint;

   tables cityid * weather / out=summary_counts (keep=cityid weather count);

run;

Are you up to the task of combining the summary data set with the original, or would you need help with that part?

[Haikuo]

As a one step alternative, you could try using Hash:

data have;
input cityid weather$ ;
cards;
1 sunny 
1 sunny 
1 cloudy 
1 rainy 
1 rainy 
1 cloudy 
1 sunny 
2 rainy 
2 rainy 
2 sunny 
2 sunny 
2 sunny 
2 sunny
2 cloudy 
;


data want;
if _n_=1 then do;
  dcl hash h();
  h.definekey('weather');
  h.definedata('count');
  h.definedone();
  end;
do until (last.cityid);
  set have;
    by cityid notsorted ;
   rc=h.find();
   if rc=0 then count+1;
   else count= 1;
   rc=h.replace();
end;

do until (last.cityid);
  set have;
    by cityid notsorted ;
   rc=h.find();
   output;
end;

rc=h.clear();
drop rc;
run;

[owenwqp1]

Thanks! Haikuo

[Ksharp · Accepted solution]

data have;
input cityid weather$ ;
n+1;
cards;
1 sunny 
1 sunny 
1 cloudy 
1 rainy 
1 rainy 
1 cloudy 
1 sunny 
2 rainy 
2 rainy 
2 sunny 
2 sunny 
2 sunny 
2 sunny
2 cloudy 
;
run;
proc sql;
create table want(drop=n) as
select *,count(*) as count
 from have 
  group by cityid,weather
   order by n;
quit;

[owenwqp1]

Thanks! Ksharp, your codes are excactly what I want!