topic Calculate average in New SAS User

Calculate average

dapenDaniel — Sun, 29 Mar 2020 07:03:37 GMT

Hi,

I have a panel dataset like below.

data have;
input Firm1 $ Firm2 $ Year flag ratio;
datalines;
AA1	BB	1995	0	0
AA1	BB	1996	0	0
AA1	BB	1997	0	0.5
AA1	BB	1998	1	0.6
AA1	BB	1999	0	0.5
AA1	CC	1995	0	0.4
AA1	CC	1996	0	0.3
AA1	CC	1997	0	0.2
AA1	CC	1998	1	0.1
AA1	CC	1999	0	0
AA2	DD	1995	0	0.9
AA2	DD	1996	1	0.5
AA2	DD	1997	0	0.4
AA2	DD	1998	1	0.6
AA2	DD	1999	0	0
AA2	EE	1995	1	0.3
AA2	EE	1996	1	0.4
AA2	EE	1997	0	0.9
AA2	EE	1998	0	0
AA2	EE	1999	0	0
;
run;

I would like to calculate the average where flag = 1 for each firm in Firm1 in each year. If there is no flag=1 in a year, the average is 0. The expected output is below.

Firm1	Year	average
AA1	1995	0
AA1	1996	0
AA1	1997	0
AA1	1998	0.35
AA1	1999	0
AA2	1995	0.3
AA2	1996	0.45
AA2	1997	0
AA2	1998	0.6
AA2	1999	0

what program do I need to use? Thanks.

Re: Calculate average

ballardw — Sun, 29 Mar 2020 08:19:01 GMT

Do you want a data set or a report?

Either approach could start with this modified data:

data have;
input Firm1 $ Firm2 $ Year flag ratio;
weightedratio = flag*ratio;
datalines;
AA1	BB	1995	0	0
AA1	BB	1996	0	0
AA1	BB	1997	0	0.5
AA1	BB	1998	1	0.6
AA1	BB	1999	0	0.5
AA1	CC	1995	0	0.4
AA1	CC	1996	0	0.3
AA1	CC	1997	0	0.2
AA1	CC	1998	1	0.1
AA1	CC	1999	0	0
AA2	DD	1995	0	0.9
AA2	DD	1996	1	0.5
AA2	DD	1997	0	0.4
AA2	DD	1998	1	0.6
AA2	DD	1999	0	0
AA2	EE	1995	1	0.3
AA2	EE	1996	1	0.4
AA2	EE	1997	0	0.9
AA2	EE	1998	0	0
AA2	EE	1999	0	0
;
run;

And then calculate the mean of the new variable.

Dataset:

proc summary data=have nway;
	class firm1 year;
	var weightedratio;
	output out=work.want (drop=_freq_ _type_)
	     mean= 
run;

Report

proc report data=have;
	columns firm1 year weightedratio;
   define firm1 / group;
   define year /  group;
   define weightedratio / mean;
run;

Re: Calculate average

Ksharp — Sun, 29 Mar 2020 12:03:01 GMT

Opps. Read OP one more time .

data have;
infile cards expandtabs truncover;
input Firm1 $ Firm2 $ Year flag ratio;
weightedratio = flag*ratio;
datalines;
AA1	BB	1995	0	0
AA1	BB	1996	0	0
AA1	BB	1997	0	0.5
AA1	BB	1998	1	0.6
AA1	BB	1999	0	0.5
AA1	CC	1995	0	0.4
AA1	CC	1996	0	0.3
AA1	CC	1997	0	0.2
AA1	CC	1998	1	0.1
AA1	CC	1999	0	0
AA2	DD	1995	0	0.9
AA2	DD	1996	1	0.5
AA2	DD	1997	0	0.4
AA2	DD	1998	1	0.6
AA2	DD	1999	0	0
AA2	EE	1995	1	0.3
AA2	EE	1996	1	0.4
AA2	EE	1997	0	0.9
AA2	EE	1998	0	0
AA2	EE	1999	0	0
;
run;
proc sql;
select Firm1,year,case when sum(flag=1)=count(*) then avg(ratio) 
when sum(flag=1)=0 then 0
else sum(flag*ratio)/sum(flag=1) end as average
 from have
  group by Firm1,year;
quit;

Re: Calculate average

dapenDaniel — Sun, 29 Mar 2020 16:39:56 GMT

Thank you, @ballardw

Re: Calculate average

dapenDaniel — Sun, 29 Mar 2020 16:40:32 GMT

Thank you, @Ksharp