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

Phil_NZ — Wed, 10 Mar 2021 05:55:05 GMT

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.

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

ballardw — Wed, 10 Mar 2021 09:20:33 GMT

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.

topic Re: How to adjust the proc means to have mean with the denominator is lag value? in SAS Programming

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

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