Assign and Retain Values to Dynamic Variable inside a macro

[Mila]

Hi all!

I am trying to create a macro to dynamically compute cumulative returns across variables in my dataset.

Issue: the macro below is overwriting the results obtained for the prior variable instead of creating separate columns in each iteration. 

Attempted solutions: 

1) Tried to assign names to %LET colname = &field; and later assigning it by: &colname =(EXP(cum_ln_return)-1); this doesnt work

2) do i need to use symput, no idea how to start here

below is my code so far:

%macro cumrets (field=); 
%LET colname = "&field"; /****** this line of code is an error - need variables to be dynamic - see below **********/
%LET var = &field;
data project.ToyData6;
set project.ToyData3;
by i;
ln_return = LOG(1+(&var));
if first.i then cum_ln_return = ln_return;
else cum_ln_return + ln_return;
&colname =(EXP(cum_ln_return)-1); /****** this line of code is an error - need variables to be dynamic, if i were to put a constant variable here like ColName code runs fine, but i only get to see results for last variable TotalReportableGrowth_lag7  **********/
run;

%mend cumrets;

%macro loop(values);
%let count=%sysfunc(countw(&values));
%do i = 1 %to &count;
%let value=%qscan(&values,&i,%str(,));
%cumrets (field=&value);
%end;
%mend;

%loop(%str(ret,
CLongGrowth_lag6,
CLongGrowth_lag7,
TotalReportableGrowth_lag1,
TotalReportableGrowth_lag2,
TotalReportableGrowth_lag3,
TotalReportableGrowth_lag4,
TotalReportableGrowth_lag5,
TotalReportableGrowth_lag6,
TotalReportableGrowth_lag7));

[Reeza]

Do you have SAS ETS?

You can check by running the following:

proc setinit;run;

[Mila]

Running SAS on WRDS - it says the follwing: looks like i might have it? 

---SAS/ETS
31JUL2019

[Reeza]

---

@Mila wrote:

Running SAS on WRDS - it says the follwing: looks like i might have it? 

---SAS/ETS
31JUL2019

---

Consider looking into PROC EXPAND as an alternative

http://www.ats.ucla.edu/stat/sas/faq/creating_tiimeseries_variables_proc_expand.htm

[Astounding]

This is much more likely to be solved using arrays than using macro language.  A little bit of macro language might be involved in order to permit different sets of variables to be processed.  But mostly, the intent is to process a set of variables with the same logic.  That screams for the use of arrays.

First. a few questions:

• Do you really have a variable named "i" that was used to sort your data set?
• Of all the variables you are computing (logs, returns, cumulative values), which ones do you actually want to keep in the final data set?  Which can be dropped after being used for computations?

[Mila]

Hey Reeza/Astounding,

the fuller code is attached and dataset. 

To keep:

Commomatch, yearbis, week3, ret, i, and last column would be the cumulative returns of each of the variables stated 

[Astounding]

If I understand what you are saying about which variables to keep, this would fit the bill for two variables.  Taking out the macro language for a moment:

data project.ToyData6;
set project.ToyData3;
by i;

array varlist {2} ret CLongGrowth_lag6;

array cumvals {2} _temporary_;

array logvals {2} _temporary_;

array returns {2} rtn_ret rtn_CLongGrowth_lag6;

do _n_=1 to 2;

   logvals{_n_} = log(1+varlist{_n_});

   if first.i then cumvals{_n_} = logvals{_n_};

   else cumvals{_n_} = cumvals{_n_} + logvals{_n_};

   returns{_n_} = exp(cumvals{_n_}) - 1;

end;

run;

Eventually, macro language will make its way into the picture.  But to start, see if this is doing what you would hope when you process two variables instead of one.

[Mila]

This line:

   returns{_n_} = exp(cumvals{_n_}) - 1;

seems to be returning the following error - not sure that i understand why...google seems to be a little vague:

ERROR: Invalid macro parameter name {. It should be a valid SAS identifier no longer than 32 characters.

[Astounding]

That message is a bit strange, since there is no macro language involved.  Is it possible to post the log?

[Mila]

Very odd- it works perfectly now without the macros-  thank you. Nice to see arrays in action in sas. 

Any chance i can nest it in a macro like below? Syntax has gotten the best of me - below code is erorring out (i hvae 115 of those variables in the actual dataset, would be nice to have it automated for the "returns" array - tried the %DO_OVER macro but didnt work)....

%macro cumretsarr (field=,cnt=);
%LET var = &field;
data project.ToyData6;
set project.ToyData3;

data project.ToyData6;
set project.ToyData3;
by i;
array varlist {cnt} &var;
array cumvals {cnt} _temporary_;
array logvals {cnt} _temporary_;
array returns {cnt} &var
/* array returns {cnt} %DO_OVER(var,PHRASE=rtn.? ) ; ******/
do _n_=1 to cnt;
logvals{_n_} = log(1+varlist{_n_});
if first.i then cumvals{_n_} = logvals{_n_};
else cumvals{_n_} = cumvals{_n_} + logvals{_n_};
returns{_n_} = exp(cumvals{_n_}) - 1;
end;
run;

%mend cumretsarr;

%macro loop(values);
%let count=%sysfunc(countw(&values));
%do i = 1 %to &count;
%let value=%qscan(&values,&i,%str(,));
/* %cumrets (field=&value,cnt=count); */
%cumretsarr (field=&value,cnt=count);
%end;
%mend;

%loop(%str(ret,
CLongGrowth_lag6,
CLongGrowth_lag7,
TotalReportableGrowth_lag1,
TotalReportableGrowth_lag2,
TotalReportableGrowth_lag3,
TotalReportableGrowth_lag4,
TotalReportableGrowth_lag5,
TotalReportableGrowth_lag6,
TotalReportableGrowth_lag7));

/************ below is part of the log file when above is run - doesnt seem to like the list &var and the %do_over macro i tried to invoke ******************/

NOTE: Line generated by the invoked macro "CUMRETSARR".
1031 data project.ToyData6; set
1031! project.ToyData3; by i; array varlist {cnt} &var; array cumvals {cnt} _temporary_; array
--- ---
---
22 22
22
202 202
202
1031! logvals {cnt} _temporary_; array returns {cnt} &var do _n_=1 to cnt; logvals{_n_}
NOTE 137-205: Line generated by the invoked macro "CUMRETSARR".
1031 data project.ToyData6; set project.ToyData3; data project.ToyData6; set
1031! project.ToyData3; by i; array varlist {cnt} &var; array cumvals {cnt} _temporary_; array
1031! logvals {cnt} _temporary_; array returns {cnt} &var do _n_=1 to cnt; logvals{_n_}
---
22
ERROR 22-322: Syntax error, expecting one of the following: an integer constant, *.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

NOTE: Line generated by the invoked macro "CUMRETSARR".
1031 data project.ToyData6; set project.ToyData3; data project.ToyData6; set
1031! project.ToyData3; by i; array varlist {cnt} &var; array cumvals {cnt} _temporary_; array
1031! logvals {cnt} _temporary_; array returns {cnt} &var do _n_=1 to cnt; logvals{_n_}
---
202
ERROR 202-322: The option or parameter is not recognized and will be ignored.

NOTE 137-205: Line generated by the invoked macro "CUMRETSARR".
1031 data project.ToyData6; set project.ToyData3; data project.ToyData6; set
1031! project.ToyData3; by i; array varlist {cnt} &var; array cumvals {cnt} _temporary_; array
1031! logvals {cnt} _temporary_; array returns {cnt} &var do _n_=1 to cnt; logvals{_n_}
-

[Astounding · Accepted solution]

To convert this to macros, you would not need to loop.  (In fact, this sort of looping is a hidden drawback to macro language.  Without looping, you need 1 DATA step.  With looping, you need 1 DATA step for each variable being processed.)

Instead, the parameters to the macro would change.  You would need to specify the entire list of incoming variables, and the entire list of variables to hold the return values.  For example:

%macro cumretarr (field_list_in=, field_list_out=);

When calling the macro, it would look something like this:

%cumretsarr (field_list_in=ret CLongGrowth_lag6 CLongGrowth_lag7 TotalReportableGrowth_lag1 TotalReportableGrowth_lag2 ... TotalReportableGrowth_lag7, field_list_out=return_ret return_CLG_lag6 return_CLG_lag7 return_TRG_lag1 ... return_TRG_lag7)

You would need to decide on the names to use to hold results for each incoming variable.  (But you would need to do that anyway, even if you try to loop through one variable at a time.)  Macro language can count how many elements are in each list, and can construct appropriate ARRAY statements.  For example:

%let cnt = %sysfunc(countw(&field_list_in));

array varlist {&cnt} &field_list_in;

array returns {&cnt} &field_list_out;

Then refer to &CNT elsewhere as needed ... the other ARRAY statements, as well as:

do _n_=1 to &CNT;

[Mila]

Thank you this was tremendously helpful! Much appreciated