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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.