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:
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;
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
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.