Calculate average

[dapenDaniel]

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.

[ballardw · Accepted solution]

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; 

[dapenDaniel]

Thank you, @ballardw 

[Ksharp]

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;

[dapenDaniel]

Thank you, @Ksharp