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 |
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;
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?
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;
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.