topic Re: How to count number of repeated value of a variable for each group in SAS Programming

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

owenwqp1 — Wed, 14 Sep 2016 16:24:37 GMT

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

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

Astounding — Wed, 14 Sep 2016 17:20:18 GMT

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?

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

Haikuo — Wed, 14 Sep 2016 18:23:31 GMT

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;

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

Ksharp — Thu, 15 Sep 2016 03:45:53 GMT

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;

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

owenwqp1 — Thu, 15 Sep 2016 07:01:22 GMT

Thanks! Haikuo

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

owenwqp1 — Thu, 15 Sep 2016 07:02:41 GMT

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