Suming - do I use proc sql?

[steppermotor]

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:

[PuneetSingh]

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]

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]

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]

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