topic calculate average in an unbalanced panel in New SAS User

calculate average in an unbalanced panel

dapenDaniel — Sun, 02 Feb 2020 07:46:16 GMT

Hi SAS Master,

I have an unbalanced panel like below.

ID	year	value
1001	1992	1
1001	1993	2
1001	1994	3
1001	1995	4
1001	1996	5
1002	2001	6
1002	2002	7
1002	2003	8
1003	1995	9
1003	1996	10
1003	1997	11
1003	1998	12
1003	1999	13
1003	2000	14
1003	2001	15
1003	2002	16
1003	2003	17
1004	2002	18
1004	2003	19

I would like to calculate the average for each year. The results I want is below.

Year	Average
1992	1
1993	2
1994	3
1995	6.5
1996	7.5
1997	11
1998	12
1999	13
2000	14
2001	10.5
2002	13.66667
2003	14.66667

I can get the "want" table by using the program below. I just want to confirm that I use the right one.

proc sql;
    create table want as select
    Year,
    avg(value) as ave_value
    from have
    group by year;
quit;

Re: calculate average in an unbalanced panel

Kurt_Bremser — Sun, 02 Feb 2020 07:57:14 GMT

The code looks good. You can also use proc summary:

proc summary data=have;
class year;
var value;
output
  out=want
  avg(value)=
;
run;

Re: calculate average in an unbalanced panel

ed_sas_member — Sun, 02 Feb 2020 11:09:01 GMT

Hi @dapenDaniel

I agree with @Kurt_Bremser's answer: your PROC SQL code looks good.

You can also use a PROC MEANS to achieve this:

proc means data=have mean noprint;
	var value;
	class year;
	ways 1;
	output out=want (drop= _type_ _freq_) mean=Average;
run;

Best,

Re: calculate average in an unbalanced panel

dapenDaniel — Sun, 02 Feb 2020 16:51:26 GMT

Thank you, @Kurt_Bremser

Re: calculate average in an unbalanced panel

dapenDaniel — Sun, 02 Feb 2020 16:51:44 GMT

Thank you, @ed_sas_member

Re: calculate average in an unbalanced panel

novinosrin — Sun, 02 Feb 2020 17:29:07 GMT

data have;
input ID	year	value;
cards;
1001	1992	1
1001	1993	2
1001	1994	3
1001	1995	4
1001	1996	5
1002	2001	6
1002	2002	7
1002	2003	8
1003	1995	9
1003	1996	10
1003	1997	11
1003	1998	12
1003	1999	13
1003	2000	14
1003	2001	15
1003	2002	16
1003	2003	17
1004	2002	18
1004	2003	19
;

data _null_;
 if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ("year") ;
   h.definedata ("year","Average","_n_","_iorc_") ;
   h.definedone () ;
 end;
 set have end=z;
 if n(value);
 if h.find()=0 then do;
   _n_=sum(_n_,1);
   _iorc_=sum(value,_iorc_);
 end;
 else do;
  _iorc_=value;
  _n_=1;
 end;
 Average=_iorc_/_n_;
 h.replace();
 if z;
 h.output(dataset:'want(drop=_:)');
run;

proc print noobs;run;

year	Average
1992	1.0000
1993	2.0000
1994	3.0000
1995	6.5000
1996	7.5000
1997	11.0000
1998	12.0000
1999	13.0000
2000	14.0000
2001	10.5000
2002	13.6667
2003	14.6667