How to adjust the proc means to have mean with the denominator is lag value?

[Phil_NZ]

Hi all,

Normally, we got the mean from proc means by the average of the variables in a group of call variables called from statement "BY".

I also did the same thing

proc means data=inter noprint;
by LOC year;
var amihud;
output out=agg_amihud_ew  mean=agg_mh_ew_year;
run;

So,agg_mh_ew_year will be calculated by the average of all firms's amihud in one country in one year. However, what I want is to calculate agg_amh_ew_year that total of all firms'amihud this year divide by the number of firms in one country last year. I think that it will relate to the variable _FREQ_ but I do not know how to adjust the code. Could you please help me to sort it out?

My novice idea is to create a datastep and then create the lag_freq, then I calculated the desired variable based on multiplying to_FREQ_ and then divide to lagfreq. Is there any other shorter idea though?

Warmest regards.

[ballardw · Accepted solution]

Using results from other values of Class or By variables means that you will need to take the results into a data step and use Lag functions or possibly use Proc SQL with a reflexive join (a table with itself) on something like

On t1.loc = t2.loc

    and t1.year=(t2.year-1)

"shorter" depends on the complexity.

Not that it reduces the complexity, such as it may be of the code, but you can request the N= statistic so you have some "nicer" than _freq_ if you don't like the variable name.