I'm confused. Are you asking for a macro that will generate:
, b.X2/a.X2 AS X2_YOY
, b.X30/a.X30 AS X30_YOY
, b.X32/a.X32 AS X32_YOY
, b.X48/a.X48 AS X48_YOY
?
Yes, that can be done like:
%macro yoy(var=) ;
%local i var_i ;
%do i=1 %to %sysfunc(countw(&var,%str( ))) ;
%let var_i=%scan(&var,&i,%str( )) ;
, b.&var_i/a.&var_i AS &var_i._YOY
%end ;
%mend yoy ;
%put %yoy(var=X2 X30 X32 X48) ;
Use like:
PROC SQL;
Create Table YoY_Selected AS
Select a.*
%yoy(var=X2 X30 X32 X48)
From My_Table AS a
Left Join My_Table AS b
on a.year=b.year+1
and a.state=b.state;
Quit;
But since you mention you're calculating a moving average, I'd encourage you to post more of the sample data you have, and describe the moving average you want to compute. It's likely there are easier ways to calculate the moving average which won't require this sort of join, or a macro.
... View more
