topic Re: How to create dynamic / moving reference in macro? in SAS Programming

How to create dynamic / moving reference in macro?

stlimpbizkit — Thu, 11 Jun 2015 17:45:56 GMT

Hello SAS community friends,

Previously I asked about Call SYMPUT function and whether it can assign values to macro variable for each record based on existing data field and retain that value for data step before moving on to the next record. The new question is similar and yet different, using a similar example below for demonstrative purpose. If you are interested, feel free to check out the original question that is helped and answered by the community: https://communities.sas.com/message/277182#277182.

A bit of context - I have a list of accounts that were opened at various point in time and hence with different performance length, measured by field "month_since_opened". The performance I am measuring is spend. Based on the available data points, I am to forecast the future spend and extrapolate the spend curve based on a known shape, represented by a series of shape factors (not shown in example for simplicity).

Acct_No	Month_since_opened	Spend_month_1	Spend_month_2	Spend_month_3	Spend_month_4	Spend_month_5	Spend_month_6	Spend_month_7	…
1	5	500	450	600	520	250	…	…	…
2	5	0	300	250	300	250	…	…	…
3	4	150	350	250	200	.	…	…	…
4	3	600	510	460	.	.	…	…	…
5	2	500	450	.	.	.	…	…	…

My challenge is to create a moving reference to produce forecast based on spend data from the trailing month, or last available month for each account to be exact. I haven't been able to write the statement correctly (in blue below).

%let count=60; /*assume we need to forecast up to 5 years/ 60 months of performance*/

data output;

set input;

array spend (60); /*In my previous post, array function was recommended as a solution to create the shell*/

do i = months_since_opened to &count;

spend (i ) = spend (i-1) / shape factor (i-1) * shape factor i /* simplified algorithm: divided spend from month i-1 by shape factor i-1 to get "normalized" spend value , then apply shape factor at month i to forecast spend at month i; shape factors are existing field by month similar to spend_month_n*/

end;

run;

Hope the way question is explained makes sense, and thanks in advance for any hint/ideas you might be able to share with me!

Re: How to create dynamic / moving reference in macro?

ballardw — Thu, 11 Jun 2015 17:57:47 GMT

Is the "data" shown the input or the output?

The pseudo-code you show references a shape factor array, you need to declare and populate that at least.

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Thu, 11 Jun 2015 18:11:31 GMT

Thanks for the feedback. That was valid point. I have updated the original content to: 1). reflect the correct input / output dataset; 2) add note that shape factors are a series of existing fields by month

Re: How to create dynamic / moving reference in macro?

Astounding — Thu, 11 Jun 2015 19:27:28 GMT

One piece of the puzzle: Your code is going to replace the Spend variables with new values. Instead, create a second set of variables to hold the forecasts. Once you do that, the need for macro language likely disappears.

array forecast {60} forecast1-forecast60;

Then:

forecast (i ) = spend (i-1) / shape factor (i-1) * shape factor i

You may not utilize forecast1, but it's easier to leave it in the program.

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Thu, 11 Jun 2015 20:59:56 GMT

Thanks for continuing to help with this case after pointing me to the right direction in part 1.

I actually did what you suggested - creating new series of fields "forecasted_spend_month_n". (Stitching actual and forecasted spend together into one string for further analysis will be another thing I need to figure out at a later point ). In this post, my challenge relies within the code.

forecast (i ) = spend (i-1) / shape factor (i-1) * shape factor i , this formula is somewhat conceptual to capture the intent. I was not able to write out the code that works, as in - When I uses "spend (i-1)" in the code, SAS wouldn't understand it is the trailing month spend that I am referring to. I tried introducing a second variable j = i-1 so that I can use j instead of i-1, but that hasn't been successful for me either.

The crux of my issue here is that (i ) and (i-1) are part of field name, and I am looking for ways to be able to refer to them in my formula respectively.

Re: How to create dynamic / moving reference in macro?

Astounding — Thu, 11 Jun 2015 21:07:14 GMT

It's a little hard to see what you have actually done, but SAS definitely understands that spend(i-1) refers to the previous month. The only time you would run into trouble with that is when i=1 and there is no previous month. The error message would say something like "Array subscript out of range."

Re: How to create dynamic / moving reference in macro?

ballardw — Thu, 11 Jun 2015 21:54:24 GMT

This really looks like you want an array for the shape factor values.
You would need to make sure the array name doesn't have spaces and the final reference would need to be shape_factor (i).

Getting the index variables to start and end as needed is a common issue with array processing.

Re: How to create dynamic / moving reference in macro?

Reeza — Thu, 11 Jun 2015 22:19:21 GMT

Have you considering transforming your data to a normalized structure - a row per month and using lag functions instead?

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Fri, 12 Jun 2015 13:11:45 GMT

Thanks for the idea Reeza. The raw dataset was structured that way - one month per record and multiple records/months per account, which then was transposed to a horizontal structure so that forecast/extrapolation is easier by creating new columns.

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Fri, 12 Jun 2015 19:13:47 GMT

I tried referring to the trailing month spend by using "spend(i-1)" or "spend(&i-1)", and got error message saying "The function SPEND is unknown, or cannot be accessed", which leads me to believe that SAS does not recognize "spend" as part of field name if I add any calculation upon i..

Re: How to create dynamic / moving reference in macro?

ballardw — Fri, 12 Jun 2015 19:20:06 GMT

Have you created an array of variables named spend? If not then SAS has to guess what "spend" is and the way you are using it makes SAS think it might be a function.

Time to show the actual code you are attempting to run.

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Fri, 12 Jun 2015 19:43:07 GMT

Thanks that makes sense. I haven't created an array named spend and hence the error message. Spend data are in the form of available columns from existing dataset, and so is shape:

spend data - as the example shown in the post (actual dataset has more records and more months of spend performance. Also there is segment field that I used to append shape later on)
shape data - has columns for shape factors by month up to 60 months that is to be covered.

Next, I merged spend data with shape data by segment. The merged dataset is the input dataset for the question posted.

%let count=60;

data output;

set input;

array spend (60);

do i = months_since_opened to &count;

spend (i ) = spend (i-1) / shape factor (i-1) * shape factor i

end;

run;

By the way I wasn't trying to hide my code since I am the one looking for suggestions. :smileycool: Just trying to simplify the case without losing necessary info. Besides what I have pasted here, the rest are a few data steps to prepare the input as described above.

Re: How to create dynamic / moving reference in macro?

Reeza — Fri, 12 Jun 2015 19:51:14 GMT

Where's the declaration for the shape array? If I understand correctly its in a separate dataset so need to merge it in somehow as well. If its a set of constants I'd consider a temporary array.

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Fri, 12 Jun 2015 20:01:03 GMT

For shape, I am actually working with dummy numbers right now to sort out the SAS code first. To come up with the right shape by segment, that is a separate piece of valuation analysis by itself. To be further transparent, I created the dummy shape like this (literally "dummy" method):

DATA SHAPE;

INPUT NAME $ shape1 shape2 shape3 shape4 shape5 shape6 shape7 shape8 shape9 shape10 shape11 shape12 shape13 shape14 shape15 shape16 shape17 shape18 shape19 shape20 shape21 shape22 shape23 shape24;

DATALINES;

spend_shape 0.2 0.56 0.65 0.73 0.80 0.86 0.91 0.95 0.98 1.00 1.02 1.04 1.06 1.08 1.10 1.11 1.12 1.13 1.14 1.15 1.16 1.17 1.18 1.19

;

Re: How to create dynamic / moving reference in macro?

Reeza — Fri, 12 Jun 2015 20:02:27 GMT

And how are you planning to pass those values to the data step indicated in your previous code where you seen to be referring to a SHAPE array already?

Re: How to create dynamic / moving reference in macro?

Reeza — Fri, 12 Jun 2015 20:04:40 GMT

Perhaps something like the following (untested):

%let count=60;

data output;

set input;

array spend (60);

array shape_factor(24) _temporary_ ( 0.2 0.56 0.65 0.73 0.80 0.86 0.91 0.95 0.98 1.00 1.02 1.04 1.06 1.08 1.10 1.11 1.12 1.13 1.14 1.15 1.16 1.17 1.18 1.19);

do i = months_since_opened to &count;

spend (i ) = spend (i-1) / shape_factor (i-1) * shape_factor( i )

end;

run;

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Fri, 12 Jun 2015 20:12:32 GMT

Those shape values are stored in dataset "shape" which is merged with "spend" and combined into "input".

I don't have anything between merging shape & spend and the piece of code with blue line above . Maybe certain steps are needed before I can create the array with relative reference (much like offset function in excel)?

Re: How to create dynamic / moving reference in macro?

Reeza — Fri, 12 Jun 2015 20:41:26 GMT

You still haven't declared the shape array then. And for the rest, I don't understand the problem anymore.

Re: How to create dynamic / moving reference in macro?

stlimpbizkit — Fri, 12 Jun 2015 20:48:24 GMT

Really appreciate for the input. Since forecasted spend has to be based on actual spend. The line spend (i ) = spend (i-1) / shape_factor (i-1) * shape_factor( i ) will let spend(i) depend on spend(i-1) which further depends on spend(i-2). Therefore, I modified your suggested code by differentiating forecast spend and actual spend below. But again, since haven't assign actual spend data into an array, SAS will assume spend(i-1) is an undeclared function of i, per ballardwpointed out earlier.

Plus, assigning shape factors this way might not be realistic dealing with thousands of segments/shapes later on. Thanks for the code though, I am piecing info from the forum and my research together and am sure will find the answer eventually/hopefully.

%let count=60;

data output;

set input;

array forecast_spend (60);

array shape_factor(24) _temporary_ ( 0.2 0.56 0.65 0.73 0.80 0.86 0.91 0.95 0.98 1.00 1.02 1.04 1.06 1.08 1.10 1.11 1.12 1.13 1.14 1.15 1.16 1.17 1.18 1.19);

do i = months_since_opened to &count;

forecast_spend (i ) = spend (i-1) / shape_factor (i-1) * shape_factor( i )

end;

run;

Log: The function spend is unknown, or cannot be accessed.

Re: How to create dynamic / moving reference in macro?

ballardw — Fri, 12 Jun 2015 20:59:03 GMT

Assuming your data set INPUT has 60 variables named SPEND1 through SPEND60 then you need:

array spend spend1 - spend60;

for the Spend(i-1) reference to work.

If the actual Spend variables in the input set are named something else they go in the place of the list where I show spend1 - spend60, ensuring the order matches the use you want. Note the - is a "from to" indicator, not subtraction.