Solved
Contributor
Posts: 50

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

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

Accepted Solutions
Solution
‎09-15-2016 03:02 AM
Super User
Posts: 10,784

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

```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;

```

All Replies
Super User
Posts: 6,785

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

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?

Posts: 3,167

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

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;
``````
Contributor
Posts: 50

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

Thanks! Haikuo
Solution
‎09-15-2016 03:02 AM
Super User
Posts: 10,784

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

```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;

```
Contributor
Posts: 50

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

Thanks! Ksharp, your codes are excactly what I want!
☑ This topic is solved.