BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stlimpbizkit
Calcite | Level 5

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.



ballardw
Super User

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.

stlimpbizkit
Calcite | Level 5

Hello everyone, with everyone's input I think I am getting close. To provide closure and give back to the group as future reference, below is the draft answer. There is more refinement to be done in my actual analysis, but it should be sufficient to address my original ask:

You can see that an extra j was introduced besides i. Variable i is to loop through future months to create forecast, while variable j is to lock in the month_since_opened for each record so that forecast starts at the incremental month based on how many months of actual spend are available.

%let count=60;

data output;

set input;

array forecast_spend (60);

array actual_spend spend1-spend60;

array shape shape1-shape60;

do i = month_since_opened+1 to &count;

%let j = max_mob;

forecast_spend(i ) = actual_spend(&j) / shape(&j) * shape (i);

end;

run;

Thanks guys for your inputs: ballardw, astounding, reeza

ballardw
Super User

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.

Reeza
Super User

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

stlimpbizkit
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 1443 views
  • 6 likes
  • 4 in conversation