DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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,020

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;


View solution in original post


All Replies
Super User
Posts: 5,498

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?

Respected Advisor
Posts: 3,156

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;
Occasional Contributor
Posts: 17

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,020

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;


Occasional Contributor
Posts: 17

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.

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

Discussion stats
  • 5 replies
  • 290 views
  • 0 likes
  • 4 in conversation