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
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.
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.