Call SYMPUT - Can it assign value to macro variable based on data step by each record/row and retain the value?

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Call SYMPUT - Can it assign value to macro variable based on data step by each record/row and retain the value?

Hello everyone,

I am trying to assign value to a macro variable based on a column from existing data set. I want to retain the value within macro variable for one row/record at a time and apply data treatment for the record before moving on to the next record.

I tried Call SYMPUT function and the result seems off, as in macro variable only retains the variable from last record.

To put things into context, below is a list of accounts for which I am keeping track of monthly spend. Accounts opened earlier will have longer history and hence more spend_month_n entries available. Take account no 1 for example, knowing it has 5 months of history I want to forecast its spend starting from month 6. Similarly, for account no 5, I need to forecast starting from month 3.

Acct_NoMonth_since_openedSpend_month_1Spend_month_2Spend_month_3Spend_month_4Spend_month_5Spend_month_6Spend_month_7
15$500$450$600$520$250
25$0$300$250$300$250
34$150$350$250$200.
43$600$510$460..
52$500$450...

Since the starting point of the forecast depends on month_since_opened, I want to assign the value of the field to a macro variable named "MSO", which then will be called for further data steps/macro to perform the forecasting. Below is the code I used:

data output;

set input;

%macro forecast(count);

call symput('mso',month_since_opened);

%do i = &mso %to &count;

forecast&i = forecasting_algorithm;              (The actual algorithm is more complex and not relevant to the question, hence simplified)

%end;

%mend forecast;

%forecast(60);                                            (I want to forecast up to 5 years / 60 months of spend, including the months accounts have already been on the book)

run;

It almost does what I wanted except for one thing - macro variable MSO seems to only retain the value of month_since_opened from the last record, which is 2. As a result, the forecasting piece overrode everything starting from month 3 including older accounts such as no 1 where we have actual spend data up to month 5.

I have tried playing around with the code but no success so far and start wondering if call symput can actually do what I want. Please shed some light and enlighten my day! Many thanks!


Accepted Solutions
Solution
‎06-09-2015 02:26 PM
Respected Advisor
Posts: 5,005

Re: Call SYMPUT - Can it assign value to macro variable based on data step by each record/row and retain the value?

You're right ... CALL SYMPUT is not designed to do what you want.  The solution would be to remove most of the macro language, along these lines:

%let count=60;

data output;

set input;
array forecast {60};

do i=month_since_opened to &count;

   forecast{i}=...;

end;

run;

If you always want to use 60 as the upper limit, you can eliminate all the macro language.  Good luck.

View solution in original post


All Replies
Solution
‎06-09-2015 02:26 PM
Respected Advisor
Posts: 5,005

Re: Call SYMPUT - Can it assign value to macro variable based on data step by each record/row and retain the value?

You're right ... CALL SYMPUT is not designed to do what you want.  The solution would be to remove most of the macro language, along these lines:

%let count=60;

data output;

set input;
array forecast {60};

do i=month_since_opened to &count;

   forecast{i}=...;

end;

run;

If you always want to use 60 as the upper limit, you can eliminate all the macro language.  Good luck.

Contributor
Posts: 23

Re: Call SYMPUT - Can it assign value to macro variable based on data step by each record/row and retain the value?

Many thanks for pointing me to the right direction. I still have many component to solve for within forecasting piece but this is a good start Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 239 views
  • 1 like
  • 2 in conversation