BookmarkSubscribeRSS Feed
steppermotor
Calcite | Level 5

I have a data set that looks like the following.I want to sum Month by for each year. Some months have no data not sure how to enter a 0 for those months that don't exist. Any thoughts appreciated.


For instance I want a table that has the following:

Month Year

Month Sum Year

1        3      2002

2        1      2002

3        1      2002

4        1      2002

5        0      2002

6        0      2002

7        3      2002

etc...

Data looks like:

4 REPLIES 4
PuneetSingh
Calcite | Level 5

Dear User,

Please find the solution as under:

/*

showing monthly cumulatives*/

data monthly;

input month year;

cards;

1 2002

1 2002

1 2002

2 2002

3 2002

7 2002

7 2002

7 2002

7 2002

1 2003

2 2003

;

run;

proc sort data = monthly; by month year; run;

data month_c;

set monthly ;

by month year;

retain cnt;

if first.year then cnt =1 ;

else cnt +1;

if last.year then output;

run;

Peter_C
Rhodochrosite | Level 12

proc summary missing data= your.data nway ;

class year month;

Output out= results( drop= _type_ ) ;

run;

However that won't provide a 0 for months with no data.

I think there is an option of the procedure to provide that csllef COMPLETETYPES

  

Good luck

peterC

Reeza
Super User

Try using the sparse option with a proc freq. That should generate the zero's as well.

proc freq data=have;

table month*year/out=want sparse;

run;

Ksharp
Super User

You need to make a dummy dataset to hold all of year and month .

data have;
input month year;
cards;
1 2002
1 2002
1 2002
2 2002
3 2002
7 2002
7 2002
7 2002
7 2002
1 2003
2 2003
;
run;
proc sort data=have(keep=year) out=year nodupkey;by year;run;
data temp;
 set year;
 do month=1 to 12;
  output;
 end;
run;
proc sql;
create table want as
select a.*,coalesce(b.sum,0) as sum
 from temp as a  left join
( select year,month,count(*) as sum
  from have 
   group by year,month)     as b
  on a.year=b.year and a.month=b.month ;
quit;




Xia Keshan

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 956 views
  • 0 likes
  • 5 in conversation