topic Suming - do I use proc sql? in SAS Procedures

Suming - do I use proc sql?

steppermotor — Sat, 05 Jul 2014 05:25:12 GMT

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:

Re: Suming - do I use proc sql?

PuneetSingh — Sat, 05 Jul 2014 06:50:53 GMT

Dear User,

Please find the solution as under:

showing monthly cumulatives*/

data monthly;

input month year;

cards;

1 2002

2 2002

3 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;

Re: Suming - do I use proc sql?

Peter_C — Sat, 05 Jul 2014 07:32:44 GMT

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

Re: Suming - do I use proc sql?

Reeza — Sat, 05 Jul 2014 19:15:42 GMT

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;

Re: Suming - do I use proc sql?

Ksharp — Sun, 06 Jul 2014 11:07:42 GMT

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