Year over Year changes

[pryagmeta]

I have worked with SAS for years but am mostly self taught and am trying to improve the robustness and flexibility of some of the legacy code I am working with. Essentially, I need to self join a table with many variables to get YoY changes, but I want to be able to just list out specific variables to include.

A simple version of the data I'm working with is like this

Year State X1 X2 X3 ... X50

2005 TX 17 5 8.... 2

2006 TX 18 9 7.... 3

While it would be easy for me to write a loop that does this join for every variable, that is computationally to much. What I want is a join like below, except easy to change the variables.

PROC SQL;

Create Table YoY_Selected AS

Select a.*

, b.X2/a.X2 AS X2_YOY

, b.X30/a.X30 AS X30_YOY

From My_Table AS a

Left Join My_Table AS b

on a.year=b.year+1

and a.state=b.state;

Quit;

Now, If I had a list, such as %Let YoY_List = X2 X30 X32 X48; Is it possible to write a macro that will it into the bottom half of the above join?

Thank you in advance for any insight or help! I've spent half a day at least on search engines and am coming up empty.

PS) Some time years are missing for some states, hence it needs to be a join (not lag in data step), plus its actually quarters and what I really need to calculate is a moving average.

[ballardw]

With this bit of your description arise a few questions: " Some time years are missing for some states, hence it needs to be a join (not lag in data step), plus its actually quarters and what I really need to calculate is a moving average."

How is the "quarter" set in your data set. Is it actually a DATE value (hint hint hint).

How would you expect missing "years" or quarters to be handled?

You show a ratio as your calculation. Are you looking for an actual moving average or moving average ratio or what?

Moving averages over time brings up the question of do you have access to Proc Expand and the other time series procs in SAS/ETS? Those are set up to do things like rolling averages over time. Though you may need to force inclusion of the dates with missing values for those periods if they aren't actually in your data.

[Quentin]

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.