topic Re: Using a List in a Macro Variable in SAS Programming

Using a List in a Macro Variable

MonsterGnom — Sat, 27 Jun 2020 13:06:08 GMT

Hey people,

I'm new to SAS and I'm sorry if this problem has a very simple solution that I'm to blind to see.

I want to define a Macro variable called "write_forward(varnames= , typelist=)" and the goal is to enter variables and the type of the variables to replace missing values of the variables with their last observation. Therefore I wrote:

%macro write_forward (varnames, typelist);

data forward;
merge sorted_spread (in=a)
sorted_transaction (in=b);
by time;

retain _&varnames.;
if &varnames. = &typelist.
then &varnames. = _&varnames.;
else _&varnames. = &varnames.;
drop _&varnames.;

run;

%mend;

%write_forward (varnames= ask
,typelist= .);

And it works fine if I only enter one variable. The problem is that as soon as I enter more than one variable in the form of:

%write_forward(varnames= ask bid

,typelist= . .)

SAS takes this as a line and enters "ask bid" as varnames instead of entering "ask" and then do it again for the next element of the line "bid".

I searched quite a while for a solution but found nothing that explains my exact problem. I hope you can help me and if there are any necessary informations missing please just tell me and I will post them.

Re: Using a List in a Macro Variable

PaigeMiller — Sat, 27 Jun 2020 13:32:27 GMT

Here's the problem. When you write a macro, and then run it, it must produce valid legal working SAS code. Your macro does not do this.

if &varnames. = &typelist.

becomes

if ask bid = . .

when you run the macro, which is not legal valid working SAS code.

You probably want a loop of some sort, but honestly, your logic in the macro doesn't make any sense to me. Specifically this part:

then &varnames. = _&varnames.;
else _&varnames. = &varnames.;

So, I couldn't even help you write a macro with a loop at this time. Please write some code without macros and without macro variables that works and does what you want it to do (you need to test it to make sure it works and does what you want, do not skip this step) so we can see what you are really trying to do.

Re: Using a List in a Macro Variable

ed_sas_member — Sat, 27 Jun 2020 13:34:37 GMT

Hi @MonsterGnom

Could you please try the following method to impute missing value using LOCF method?

No need to use a macroprogram.

data have;
	merge sorted_spread (in=a) sorted_transaction (in=b);
	by time;
	id=1; /*identifier to group all observations together*/
run;

data forward (drop=id);
   update have(obs=0) have;
   by id;
   output;
run;

Re: Using a List in a Macro Variable

MonsterGnom — Sat, 27 Jun 2020 13:52:44 GMT

First of all thanks for the answer. I think I see your point but my problem is that there are guidelines in the assignment I have to do. Earlier in the code we have to do the same task of writing missing values forward using a retain statement what lead me to

retain _ask;
if ask ="."
then ask = _ask;
else _ask = ask;
drop _ask;

and it does what it is supposed to do. Problem is another task wants me to do the same using this macro variable. The text of the task:

"12. Again, merge the sorted datasets sorted_spread and sorted_transaction in a data step. However, now associate the transaction price with the bid-ask-spread at the beginning of the subsequent second after the trade. To do so, use the macro write_forward to write all the variables from the transaction dataset forward. Inparticular, the macro write_forward replaces the missing values of the variables. The list typelist specifies whether missing values are encoded with a dot.(numeric variables) or an emptystring""(character variables).
/* Example for a call of the macro*/
%write_forward(varnames=char_var1 num_var1 char_var2
,typelist="" . "");
After that, remove all the observations that still contain missing values in any of the variables price, ask, or bid. Perform all of these steps in a single data step. Call the resulting dataset forward."

I thought the easiest way to do it would be using what I did before and just put it in a macro variable. And about the retain statement I thought I just use a new variable called _ask that remembers the last missing value and if the value in the original variable is missing it is replaced by the new _ask variable. If its not missing the _ask variable takes on the new value that is now the last non-missing one. At the end the new variable _ask is dropped because its pretty much only a helping hand. I hope this at least explains what I was doing.

Re: Using a List in a Macro Variable

MonsterGnom — Sat, 27 Jun 2020 13:56:06 GMT

Thanks for your answer. I would very much like to do it without a macro that is not necessary but as i wrote in my reply above I'm forced to do so as well as I'm forced to use a retain statement to write it forward in the beginning.

Re: Using a List in a Macro Variable

Kurt_Bremser — Sat, 27 Jun 2020 14:12:37 GMT

So you need to repeat these statements

retain _ask;
if ask ="."
then ask = _ask;
else _ask = ask;
drop _ask;

for a series of variables.

Step 1: replace explicit varnames with macro variable, and set the macrovar

%let var=ask;
retain _&var.;
if &var. ="."
then &var. = _&var.;
else _&var. = &var.;
drop _&var.;

To iterate through your variable list, do

%do i = 1 %to %sysfunc(countw(&varnames.));
  %let var = %scan(&varnames.,&i.);
  /* your code to repeat */
%end;

Re: Using a List in a Macro Variable

PaigeMiller — Sat, 27 Jun 2020 14:20:14 GMT

While @Kurt_Bremser is pretty close, the macro won't work on a mix of character and numeric variables without additional complications. If you have a character variable, then &var='.' will work, but it won't work for numeric variables. So, are you really in the situation where one variable in &varnames could be character and another variable in &varnames could be numeric?

Re: Using a List in a Macro Variable

Tom — Sat, 27 Jun 2020 16:05:43 GMT

There is an issue with the code sample you provided for a single variable. If the variable is numeric then the test condition is wrong because it is comparing a numeric value to a character value. If the variable is character then you will make the new retained variable as numeric instead of as character. That is because the first place it is used (first place where SAS needs to make a decision on the type, the RETAIN doesn' t need to know the type) is on the right side of an assignment statement, so SAS will assume the new variable is numeric. This is probably why the macro is designed to take in TWO lists, one with the names and one with the types.

You can fix that by changing the code just a little bit. (Code formatting note: do not split a single statement into two lines without at least adding some indentation). By using the MISSING() function you eliminate the need to know the type in advance. By reversing the test condition you can have the new variable first appear on the LEFT side of the assignment statement so that SAS will create it to match the type of the value on the right side of the assignment statement. Note that since the RETAIN statement, like the DROP statement, is not executable there is no need to have it first.

if not missing(ask) then _ask = ask;
else ask=_ask;
retain _ask;
drop _ask;

So now your macro does not even need the TYPELIST parameter. And there is no need to include the rest of the data step in the macro, just the lines of data step code that you want to generate. Just make sure to call the macro inside a data step so that the code is generated in a place where it makes sense. You might want to write a note to the log if anyone tries to use the TYPELIST parameter.

%macro write_forward (varnames, typelist);
%local i var ;
%if %length(&typelist) %then %put NOTE: Ignoring TYPELIST input as it is not needed. &=typelist;
%do i=1 %to %sysfunc(countw(&varnames,%str( )));
  %let var=%scan(&varnames,%str( ));
if not missing(&var ) then _&var= &var;
else &var=_&var;
retain _&var;
drop _&var;
%end;
%mend write_forward;

And you could use it like this to deal with 4 variables.

data forward;
  merge sorted_spread (in=a)
        sorted_transaction (in=b)
  ;
  by time;
  %write_forward (varnames= ask a b c)
run;

Re: Using a List in a Macro Variable

Reeza — Sat, 27 Jun 2020 16:21:37 GMT

Sounds like this is homework then.

@Kurt_Bremsers given you all the tools you need to solve this in his most recent answer.

You can also reference the macro library appendix for sample code on creating loops to loop through a list if you want or you can look into call execute to run your macro multiple times for different variables.

I prefer the second approach as I find it much easier to debug.

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage - macro appendix
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@MonsterGnom wrote:
Thanks for your answer. I would very much like to do it without a macro that is not necessary but as i wrote in my reply above I'm forced to do so as well as I'm forced to use a retain statement to write it forward in the beginning.

Re: Using a List in a Macro Variable

MonsterGnom — Sat, 27 Jun 2020 17:44:25 GMT

Thanks a lot for your answer. First it didnt work cause I put the do statement before the data step but found the solution. So you're my todays hero KurtBremser.