BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
owenwqp1
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

5 REPLIES 5
Astounding
PROC Star

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?

Haikuo
Onyx | Level 15

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;
Ksharp
Super User
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;


owenwqp1
Obsidian | Level 7
Thanks! Ksharp, your codes are excactly what I want!

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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